﻿Imports Microsoft.Office.Interop.Owc11
Imports System.Text.RegularExpressions

Partial Class WebForm_ManageSOSalesForecast_SF_INPUT
    Inherits System.Web.UI.UserControl
    Protected stManager As New SignatureManager
    Protected sfmanager As New SFSapcodeManager

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Me.BindDDLTransferPLV()
            Me.ProductHierarchyControl1.BindData(Me.Division, Me.ViewPoint)
            Me.DefaultReportMode = Me.Search2.ReportMode
        End If
    End Sub

#Region "事件"
    '送出查詢
    Protected Sub ProductHierarchyControl1_Submit(ByVal sender As Object, ByVal e As System.EventArgs) Handles ProductHierarchyControl1.Submit
        Me.BindXML()
    End Sub

    '重新整理
    Protected Sub btnRefreshSF_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Me.BindXML()
    End Sub

    '另開視窗顯示
    Protected Sub ProductHierarchyControl1_SubmitPop(ByVal sender As Object, ByVal e As System.EventArgs) Handles ProductHierarchyControl1.SubmitPop
        Me.SetSessionData()
        Dim url As String = String.Format("~/Webform/ManageSalesForecastPop.aspx?Meeting=Y&Division={0}&SessionID={1}", Me.Division, Me.SessionID)
        Dim open As String = "window.open('" + Me.ResolveUrl(url) + "','newwindow','height=575px,toolbar=0,titlebar=0,resizable,scrollbars=Yes,status=1');"
        ScriptManager.RegisterClientScriptBlock(TryCast(HttpContext.Current.Handler, Page), GetType(String), "onclick", open.ToString, True)
    End Sub

    '匯出單一檔案
    Protected Sub ProductHierarchyControl1_Export(ByVal sender As Object, ByVal e As System.EventArgs) Handles ProductHierarchyControl1.Export
        Me.BindXML()
        If MyObj.GetParameter("Export", "ExportType").ToUpper() <> "XLS" Then
            Dim fileName1 As String = "SFReportExport(" & SFModel.SF_SignatureText & ").xml"
            Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName1)
            Response.ContentType = "application/vnd.ms-excel"
            Response.Write(Me.Session(Me.SessionID))
            Response.End()
        End If

        Dim MyRandom As New clsRandom
        Dim FileName As String = Me.MapPath("~\tmp\") & MyRandom.Generate(8)

        Dim sr As New IO.StreamWriter(FileName, True)
        sr.WriteLine(Me.Session(Me.SessionID))
        sr.Close()

        'Xml轉Xlsx
        Dim MyExport As New clsExport
        MyExport.XmlToXlsx(FileName, String.Format("SO_SFReportExport(" & SFModel.SF_SignatureText & ")"))
    End Sub

    '匯出多個檔案(By PH)
    Protected Sub ProductHierarchyControl1_ExportGroupByPH(ByVal sender As Object, ByVal e As System.EventArgs) Handles ProductHierarchyControl1.ExportGroupByPH
        Me.ExportGroup("PH")
    End Sub

    '匯出多個檔案(By CG)
    Protected Sub ProductHierarchyControl1_ExportGroupByCG(ByVal sender As Object, ByVal e As System.EventArgs) Handles ProductHierarchyControl1.ExportGroupByCG
        Me.ExportGroup("CG")
    End Sub

    Protected Sub ExportGroup(ByVal ExportBy As String)
        Me.BindYear()
        SFModel.InitData()
        SFModel.SF_Division = Me.Division
        SFModel.ViewPoint = Me.ViewPoint
        SFModel.HeaderTB = Me.HeaderTB
        SFModel.SF_MonthCode = Me.ProductHierarchyControl1.MonthCode
        SFModel.SF_Version1 = "S" 'Me.ProductHierarchyControl1.Version1
        SFModel.SF_Version2 = Me.ProductHierarchyControl1.Version2
        SFModel.SF_Signature = Me.ProductHierarchyControl1.Signature
        SFModel.Coefficient = Myobj.GetCoefficient(SFModel.SF_Signature)
        SFModel.SF_SignatureText = Me.ProductHierarchyControl1.SignatureText
        SFModel.SF_SecondLevelName = Me.ProductHierarchyControl1.SecondLevelName
        SFModel.SF_SecondLevelValue = Me.ProductHierarchyControl1.SecondLevelValue
        SFModel.SF_sqlArray = Me.ProductHierarchyControl1.sqlArray
        SFModel.SF_Channel = Me.ProductHierarchyControl1.Channel
        SFModel.SF_Year = Me.ProductHierarchyControl1.Year
        SFModel.SF_Month = Me.ProductHierarchyControl1.Month
        SFModel.SF_SpecialMonth = Me.ProductHierarchyControl1.SpecialMonth
        SFModel.SF_FCSTMonth = Me.ProductHierarchyControl1.FCSTMonth
        SFModel.SF_ReferYYMM = Me.ProductHierarchyControl1.ReferYYMM
        SFModel.SF_ReferMM = Me.ProductHierarchyControl1.ReferMM
        SFModel.SF_MMPP = Me.ProductHierarchyControl1.MMPP
        SFModel.SF_IsCatagory = Me.ProductHierarchyControl1.IsCatagory
        SFModel.SF_BasicSignature = Me.ProductHierarchyControl1.BasicSignature
        SFModel.SF_DisplayYear = Me.ProductHierarchyControl1.DisplayYear
        SFModel.ShowOneSummary = Me.ProductHierarchyControl1.ShowOneSummary
        SFModel.MonthData = Me.MonthData
        If Me.ProductHierarchyControl1.SFShowType = "Gross" Then
            Me.Search2.ReportMode = ReportModeE.Gross
            SFModel.SF_ReportMode = ReportModeE.Gross
        Else
            Me.Search2.ReportMode = Me.DefaultReportMode
            SFModel.SF_ReportMode = Me.DefaultReportMode
        End If
        SFModel.ColumnSection = Me.ColumnSection
        SFModel.MeetingSummary = Me.MeetingSummary
        '先清掉3階以後的資料
        If SFModel.SF_sqlArray.Length > 2 Then
            For i As Integer = 2 To SFModel.SF_sqlArray.GetUpperBound(0)
                SFModel.SF_sqlArray(i, 1) = ""
            Next
        End If

        If Me.ProductHierarchyControl1.SecondLevelValue = "" Then
            If Me.ProductHierarchyControl1.Channel = "" Then
                '無第二階,無Channel
                SFModel.SF_SheetName = SFModel.SF_BasicSignature & "_" & "All" & "_" & "All"
                SFModel.BindData()

                '無第二階,全部Channel
                If ExportBy = "CG" Then
                    For i As Integer = 0 To Me.ProductHierarchyControl1.ChannelList.Rows.Count - 1
                        Dim MyRow As DataRow = Me.ProductHierarchyControl1.ChannelList.Rows(i)
                        SFModel.SF_sqlArray(1, 1) = ""
                        SFModel.SF_SecondLevelValue = ""
                        SFModel.SF_Channel = MyRow("ChannelValue").ToString()
                        SFModel.SF_SheetName = SFModel.SF_BasicSignature & "_" & "All" & "_" & MyRow("ChannelName").ToString().Replace("'", "’")
                        SFModel.BindData()
                    Next
                End If

                If ExportBy = "PH" Then
                    '有第二階,無Channel
                    For i As Integer = 0 To Me.ProductHierarchyControl1.SecondLevelList.Rows.Count - 1
                        Dim MyRow As DataRow = Me.ProductHierarchyControl1.SecondLevelList.Rows(i)
                        SFModel.SF_sqlArray(1, 1) = MyRow("HierarchyValue").ToString()
                        SFModel.SF_SecondLevelValue = MyRow("HierarchyValue").ToString()
                        SFModel.SF_Channel = ""
                        SFModel.SF_SheetName = SFModel.SF_BasicSignature & "_" & MyRow("HierarchyValue").ToString() & "_" & "All"
                        SFModel.BindData()
                    Next
                End If
            Else
                '無第二階,有Channel
                SFModel.SF_SheetName = SFModel.SF_BasicSignature & "_" & "All" & "_" & Me.ProductHierarchyControl1.ChannelText.ToString().Replace("'", "’")
                SFModel.BindData()
                If ExportBy = "PH" Then
                    For i As Integer = 0 To Me.ProductHierarchyControl1.SecondLevelList.Rows.Count - 1
                        Dim MyRow As DataRow = Me.ProductHierarchyControl1.SecondLevelList.Rows(i)
                        SFModel.SF_sqlArray(1, 1) = MyRow("HierarchyValue").ToString()
                        SFModel.SF_SecondLevelValue = MyRow("HierarchyValue").ToString()
                        SFModel.SF_SheetName = SFModel.SF_BasicSignature & "_" & MyRow("HierarchyValue").ToString() & "_" & Me.ProductHierarchyControl1.ChannelText.ToString().Replace("'", "’")
                        SFModel.BindData()
                    Next
                End If
            End If
        Else
            If Me.ProductHierarchyControl1.Channel = "" Then
                '有第二階,無Channel
                SFModel.SF_SheetName = SFModel.SF_BasicSignature & "_" & Me.ProductHierarchyControl1.SecondLevelValue & "_" & "All"
                SFModel.BindData()
                If ExportBy = "CG" Then
                    For i As Integer = 0 To Me.ProductHierarchyControl1.ChannelList.Rows.Count - 1
                        Dim MyRow As DataRow = Me.ProductHierarchyControl1.ChannelList.Rows(i)
                        SFModel.SF_Channel = MyRow("ChannelValue").ToString()
                        SFModel.SF_SheetName = SFModel.SF_BasicSignature & "_" & Me.ProductHierarchyControl1.SecondLevelValue & "_" & MyRow("ChannelName").ToString().Replace("'", "’")
                        SFModel.BindData()
                    Next
                End If
            Else
                '有第二階,有Channel
                SFModel.SF_SheetName = SFModel.SF_BasicSignature & "_" & Me.ProductHierarchyControl1.SecondLevelValue & "_" & Me.ProductHierarchyControl1.ChannelText.ToString().Replace("'", "’")
                SFModel.BindData()
            End If
        End If

        If MyObj.GetParameter("Export", "ExportType").ToUpper() <> "XLS" Then
            Dim fileName1 As String = String.Format("SFReportExportGroupBy{0}(" & SFModel.SF_SignatureText & ").xml", ExportBy)
            Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName1)
            Response.ContentType = "application/vnd.ms-excel"
            Response.Write(SFModel.GetXMLDataAll())
            Response.End()
        Else
            Dim MyRandom As New clsRandom
            Dim FileName As String = Me.MapPath("~\tmp\") & MyRandom.Generate(8)

            Dim sr As New IO.StreamWriter(FileName, True)
            sr.WriteLine(SFModel.GetXMLDataAll())
            sr.Close()

            'Xml轉Xlsx
            Dim MyExport As New clsExport
            MyExport.XmlToXlsx(FileName, String.Format("SO_SFReportExportGroupBy{0}(" & SFModel.SF_SignatureText & ")", ExportBy))
        End If
    End Sub

    '設定可管理的CustomerGroup Percentage
    Protected Sub SetPercentage()
        Dim cmanager As New ChannelManager
        Me.ChannelPercentage = cmanager.GetChannelPercentage(Me.Division, Me.ProductHierarchyControl1.BasicSignature, Me.ProductHierarchyControl1.SecondLevelName, Me.ProductHierarchyControl1.SecondLevelValue, Me.ProductHierarchyControl1.Channel)
    End Sub

    '串資料,並顯示XML到畫面上
    Private Sub BindXML()
        Me.BindYear()
        Me.SetSessionData()

        SFModel.InitData()
        SFModel.SF_Division = Me.Division
        SFModel.ViewPoint = Me.ViewPoint
        SFModel.HeaderTB = Me.HeaderTB
        SFModel.SF_MonthCode = Me.ProductHierarchyControl1.MonthCode
        SFModel.SF_Version1 = "S" 'Me.ProductHierarchyControl1.Version1
        SFModel.SF_Version2 = Me.ProductHierarchyControl1.Version2
        SFModel.SF_Signature = Me.ProductHierarchyControl1.Signature
        SFModel.Coefficient = Myobj.GetCoefficient(SFModel.SF_Signature)
        SFModel.SF_SignatureText = Me.ProductHierarchyControl1.SignatureText
        SFModel.SF_SecondLevelName = Me.ProductHierarchyControl1.SecondLevelName
        SFModel.SF_SecondLevelValue = Me.ProductHierarchyControl1.SecondLevelValue
        SFModel.SF_sqlArray = Me.ProductHierarchyControl1.sqlArray
        SFModel.SF_Channel = Me.ProductHierarchyControl1.Channel
        SFModel.SF_Year = Me.ProductHierarchyControl1.Year
        SFModel.SF_Month = Me.ProductHierarchyControl1.Month
        SFModel.SF_SpecialMonth = Me.ProductHierarchyControl1.SpecialMonth
        SFModel.SF_FCSTMonth = Me.ProductHierarchyControl1.FCSTMonth
        SFModel.SF_ReferYYMM = Me.ProductHierarchyControl1.ReferYYMM
        SFModel.SF_ReferMM = Me.ProductHierarchyControl1.ReferMM
        SFModel.SF_MMPP = Me.ProductHierarchyControl1.MMPP
        SFModel.SF_IsCatagory = Me.ProductHierarchyControl1.IsCatagory
        SFModel.SF_BasicSignature = Me.ProductHierarchyControl1.BasicSignature
        SFModel.SF_DisplayYear = Me.ProductHierarchyControl1.DisplayYear
        SFModel.ShowOneSummary = Me.ProductHierarchyControl1.ShowOneSummary
        SFModel.MonthData = Me.MonthData
        If Me.ProductHierarchyControl1.SFShowType = "Gross" Then
            Me.Search2.ReportMode = ReportModeE.Gross
            SFModel.SF_ReportMode = ReportModeE.Gross
        Else
            Me.Search2.ReportMode = Me.DefaultReportMode
            SFModel.SF_ReportMode = Me.DefaultReportMode
        End If
        SFModel.ColumnSection = Me.ColumnSection
        SFModel.MeetingSummary = Me.MeetingSummary

        '顯示Sales Quantity / Gross Sales,Add by Angus 2011/8/2
        '=========================================================
        If Me.ProductHierarchyControl1.SFShowType = "Gross" Then
            'Me.DisplayControl(Me.Search2.FindControl("btnGrossSales"), False)
            Me.DisplayControl(Me.Search2.FindControl("lblNotes"), False)
            Me.DisplayControl(Me.Search2.FindControl("txtNotes"), False)
            Me.DisplayControl(Me.Search2.FindControl("btnUpload"), False)
            Me.DisplayControl(Me.Search2.FindControl("btnPSpectrum"), False)
            Me.DisplayControl(Me.Search2.FindControl("btnPLV"), False)
            Me.DisplayControl(Me.Search2.FindControl("lblTransferPLV"), False)
            Me.DisplayControl(Me.Search2.FindControl("ddlTransferPLV"), False)
            Me.DisplayControl(Me.Search2.FindControl("btnTransferPLV"), False)
            Me.DisplayControl(Me.Search2.FindControl("btnRefreshSF"), True)
            Me.DisplayControl(Me.Search2.FindControl("btnSave"), False)
        Else
            'Me.DisplayControl(Me.Search2.FindControl("btnGrossSales"), True)
            Me.DisplayControl(Me.Search2.FindControl("lblNotes"), True)
            Me.DisplayControl(Me.Search2.FindControl("txtNotes"), True)
            Me.DisplayControl(Me.Search2.FindControl("btnUpload"), True)
            Me.DisplayControl(Me.Search2.FindControl("btnPSpectrum"), True)
            Me.DisplayControl(Me.Search2.FindControl("btnPLV"), True)
            Me.DisplayControl(Me.Search2.FindControl("lblTransferPLV"), True)
            Me.DisplayControl(Me.Search2.FindControl("ddlTransferPLV"), True)
            Me.DisplayControl(Me.Search2.FindControl("btnTransferPLV"), True)
            Me.DisplayControl(Me.Search2.FindControl("btnRefreshSF"), False)
            Me.DisplayControl(Me.Search2.FindControl("btnSave"), True)
        End If
        '=========================================================
        Me.SFModel.SF_SheetName = Me.GetSheetName()
        'MyObj.WriteLog(SFModel.GetXMLDataOne())
        Me.ReportXML = SFModel.GetXMLDataOne()
        Session(Me.SessionID & "SapCodeDT") = Me.SFModel.SapCodeDT
        'MyObj.WriteLog(Me.ReportXML)

        Me.pl1.Visible = True
        Me.Search2.DataBind()
        '選Tatol時,隱藏Save按鈕
        If Me.Search2.FindControl("btnSave") IsNot Nothing Then
            'If Me.IsMutiSignature Or Me.Search2.ReportMode = ReportModeE.Gross Then
            'Meeting時,選Total或Group Signature必須可以修改
            If Me.Search2.ReportMode = ReportModeE.Gross Then
                Me.Search2.FindControl("btnSave").Visible = False
                Me.Search2.FindControl("btnPercentage").Visible = False
            Else
                Me.Search2.FindControl("btnSave").Visible = True
                Me.Search2.FindControl("btnPercentage").Visible = True
            End If
        End If

        Me.VControl = 1
        Dim btControl As Control = Me.Search2.FindControl("btnSave")
        If btControl IsNot Nothing Then
            If Request.Url.PathAndQuery.Contains("SOSalesForecast") Then
                'ProgramID = clsAuth.GetProgramID(Request.Url.PathAndQuery.Substring(Request.Url.PathAndQuery.Split("/").Length, Request.Url.PathAndQuery.Length - Request.Url.PathAndQuery.Split("/").Length).Replace("/", "\"))
                ProgramID = clsAuth.GetProgramID(clsAuth.GetProgramUrl)

                If ProgramID <> "" Then
                    ProgramParent = clsAuth.GetParentProgramID(ProgramID)
                End If
            End If

            If ProgramID <> "" And ProgramParent <> 0 Then
                Dim tempButton As New Button
                MyObj.IsVisibleControl(tempButton, ProgramParent, ProgramID)
                If tempButton.Visible = False Then
                    Me.VControl = 0
                Else
                    Me.VControl = 1
                End If

                If btControl.Visible = True Then
                    btControl.Visible = tempButton.Visible
                End If

                If btControl.Visible = False Then
                    Session(Me.SessionID & "SF_Lock") = True
                End If
            End If
        End If
    End Sub
#End Region

#Region "繫結資料"
    Private Sub BindYear()
        Dim txtDailyInputYear As TextBox = Me.Search2.FindControl("txtDailyInputYear")
        txtDailyInputYear.Text = Me.ProductHierarchyControl1.Year.ToString()
    End Sub
    Private Sub BindDDLTransferPLV()
        Dim ddlControl As DropDownList = Me.Search2.FindControl("ddlTransferPLV")
        ddlControl.DataTextField = "Text"
        ddlControl.DataValueField = "Value"
        ddlControl.DataSource = MyObj.GetCodeMappingData("Product", "MPStatus_PLV")
        ddlControl.DataBind()
        ddlControl.Items.Insert(0, MyObj.GetDDLSelectAllItem())
    End Sub
#End Region

#Region "方法"
    Protected Function GetSheetName() As String
        Dim SheetName As String = ""
        If Me.SFModel.SF_SecondLevelValue = "" Then
            If Me.SFModel.SF_Channel = MyObj.SelectAllValue Then
                SheetName = Me.SFModel.SF_BasicSignature & "-" & "ALL" & "-" & "ALL"
            Else
                SheetName = Me.SFModel.SF_BasicSignature & "-" & "ALL" & "-" & Me.ProductHierarchyControl1.ChannelText.ToString().Replace("'", "’")
            End If
        Else
            If Me.SFModel.SF_Channel = MyObj.SelectAllValue Then
                SheetName = Me.SFModel.SF_BasicSignature & "-" & Me.SFModel.SF_SecondLevelValue & "-" & "ALL"
            Else
                SheetName = Me.SFModel.SF_BasicSignature & "-" & Me.SFModel.SF_SecondLevelValue & "-" & Me.ProductHierarchyControl1.ChannelText.ToString().Replace("'", "’")
            End If
        End If
        Return SheetName
    End Function

    Private Sub DisplayControl(ByVal cName As Control, ByVal Display As Boolean)
        If cName IsNot Nothing Then
            cName.Visible = Display
        End If
    End Sub

    '設定Session資料
    Private Sub SetSessionData()
        If Search2.ReportMode <> ReportModeE.Gross And Search2.ReportMode <> ReportModeE.Act Then
            Me.SetPercentage()
            Me.SetCatagoryAuth()
            Me.SFModel.SetCatagoryAuth()
            Session(Me.SessionID & "SF_CatagoryString") = Me.CatagoryString
            Session(Me.SessionID & "SF_ViewPoint") = Me.ViewPoint
            Session(Me.SessionID & "SF_HeaderTB") = Me.ViewPoint.Replace("SF", "").Replace("TC", "").Replace("SM", "")
            Session(Me.SessionID & "SF_Division") = Me.Division
            Session(Me.SessionID & "SF_MonthCode") = Me.ProductHierarchyControl1.MonthCode
            Session(Me.SessionID & "SF_Version1") = "S" 'Me.ProductHierarchyControl1.Version1
            Session(Me.SessionID & "SF_Version2") = Me.ProductHierarchyControl1.Version2
            Session(Me.SessionID & "SF_Signature") = Me.ProductHierarchyControl1.Signature
            Session(Me.SessionID & "SF_SignatureText") = Me.ProductHierarchyControl1.SignatureText
            Session(Me.SessionID & "SF_SecondLevelName") = Me.ProductHierarchyControl1.SecondLevelName
            Session(Me.SessionID & "SF_SecondLevelValue") = Me.ProductHierarchyControl1.SecondLevelValue
            Session(Me.SessionID & "SF_sqlArray") = Me.ProductHierarchyControl1.sqlArray
            Session(Me.SessionID & "SF_Channel") = Me.ProductHierarchyControl1.Channel
            Session(Me.SessionID & "SF_Year") = Me.ProductHierarchyControl1.Year
            Session(Me.SessionID & "SF_Month") = Me.ProductHierarchyControl1.Month
            Session(Me.SessionID & "SF_SpecialMonth") = Me.ProductHierarchyControl1.SpecialMonth
            Session(Me.SessionID & "SF_FCSTMonth") = Me.ProductHierarchyControl1.FCSTMonth
            Session(Me.SessionID & "SF_ReferYYMM") = Me.ProductHierarchyControl1.ReferYYMM
            Session(Me.SessionID & "SF_ReferMM") = Me.ProductHierarchyControl1.ReferMM
            Session(Me.SessionID & "SF_MMPP") = Me.ProductHierarchyControl1.MMPP
            Session(Me.SessionID & "SF_IsCatagory") = Me.ProductHierarchyControl1.IsCatagory
            Session(Me.SessionID & "SF_BasicSignature") = Me.ProductHierarchyControl1.BasicSignature
            Session(Me.SessionID & "SF_DisplayYear") = Me.ProductHierarchyControl1.DisplayYear
            Session(Me.SessionID & "SF_IsMutiSignature") = Me.SFModel.IsMutiSignature

            '鎖住畫面
            Dim lock As Boolean = False
            If Session(Me.SessionID & "SF_Year") < DateTime.Now.Year Then
                lock = True
            ElseIf Session(Me.SessionID & "SF_Year") = DateTime.Now.Year Then
                If Not Me.Session(Me.SessionID & "SF_SpecialMonth") AndAlso Session(Me.SessionID & "SF_Month") < DateTime.Now.Month Then
                    lock = True
                End If
            End If
            If lock = False Then
                Dim vcManager As New VersionManager(Me.ProductHierarchyControl1.MonthCode, Me.ProductHierarchyControl1.Signature, Me.HeaderTB)
                lock = vcManager.Locked
                'Sales Forecast 全部LOCK
                'lock = True
            End If
            SFModel.SF_Lock = lock
            Session(Me.SessionID & "SF_Lock") = lock
        End If
    End Sub
    '設定新分類的權限
    Private Sub SetCatagoryAuth()
        Dim MyCatagoryString As New StringBuilder
        If Me.ProductHierarchyControl1.IsCatagory Then
            Dim lManager As New LevelManager
            Dim MyRow As DataRow = lManager.GetLevelName2(Me.ProductHierarchyControl1.Division, Me.ViewPoint, 2)
            If MyRow IsNot Nothing Then
                Dim ItemCodes As String = MyObj.JoinFormat(",", "'{0}'", lManager.GetItemCodes(Me.ProductHierarchyControl1.Division, MyRow("LevelName"), Me.ProductHierarchyControl1.SignatureText))

                If ItemCodes <> "" Then
                    If MyRow("Chain") = "Y" Then
                        MyCatagoryString.AppendFormat("AND {0} in ", String.Format("substring(Hierarchy,1,{0}*2)", MyRow("Sequence")))
                    Else
                        MyCatagoryString.AppendFormat("AND {0} in ", String.Format("substring(Hierarchy,{0}*2-1,2)", MyRow("Sequence")))
                    End If
                    MyCatagoryString.AppendFormat("({0}) ", ItemCodes)
                End If
            End If

            MyRow = lManager.GetLevelName2(Me.ProductHierarchyControl1.Division, Me.ViewPoint, 3)
            If MyRow IsNot Nothing Then
                Dim ItemCodes As String = MyObj.JoinFormat(",", "'{0}'", lManager.GetItemCodes(Me.ProductHierarchyControl1.Division, MyRow("LevelName"), Me.ProductHierarchyControl1.SignatureText))

                If ItemCodes <> "" Then
                    If MyRow("Chain") = "Y" Then
                        MyCatagoryString.AppendFormat("AND {0} in ", String.Format("substring(Hierarchy,1,{0}*2)", MyRow("Sequence")))
                    Else
                        MyCatagoryString.AppendFormat("AND {0} in ", String.Format("substring(Hierarchy,{0}*2-1,2)", MyRow("Sequence")))
                    End If
                    MyCatagoryString.AppendFormat("({0}) ", ItemCodes)
                End If
            End If
        End If
        Me.CatagoryString = MyCatagoryString.ToString()
    End Sub

    Private _MonthData As DataTable

    Public ReadOnly Property MonthData() As DataTable
        Get
            If _MonthData Is Nothing Then
                Dim dat As New clsData
                Dim sql As New StringBuilder
                Dim params As New List(Of SqlParameter)

                Dim dt As New DataTable

                dt.Columns.Add("Month", GetType(Date))
                dt.Columns.Add("Flag")
                dt.Columns.Add("Curr")

                Dim firstMonth As Date
                '前一年的Act改用Pop Window顯示,Update By Angus 20101117
                If Me.Search2.ReportMode = ReportModeE.Act OrElse Me.ProductHierarchyControl1.DisplayYear = "-1" Then
                    firstMonth = New Date(Me.ProductHierarchyControl1.Year - 1, 1, 1)

                    'ACT
                    For i As Integer = 0 To 12 - 1
                        Dim dr As DataRow = dt.NewRow
                        Dim current As Date = firstMonth.AddMonths(i)

                        dr("Month") = current
                        dr("Flag") = "ACT"

                        dt.Rows.Add(dr)
                    Next
                ElseIf Me.ProductHierarchyControl1.DisplayYear = "" Then
                    firstMonth = New Date(Me.ProductHierarchyControl1.Year - 1, 1, 1)

                    'ACT
                    For i As Integer = 0 To 12 - 1
                        Dim dr As DataRow = dt.NewRow
                        Dim current As Date = firstMonth.AddMonths(i)

                        dr("Month") = current
                        dr("Flag") = "ACT"

                        dt.Rows.Add(dr)
                    Next


                    'FCST
                    '預計總月份
                    Dim totalMons As Integer = 15
                    '今年佔用月份
                    Dim currMons As Integer = 13 - Me.ProductHierarchyControl1.FCSTMonth
                    firstMonth = New Date(Me.ProductHierarchyControl1.Year, 1, 1)

                    For i As Integer = 0 To 12 - 1
                        Dim dr As DataRow = dt.NewRow
                        Dim current As Date = firstMonth.AddMonths(i)

                        dr("Month") = current
                        dr("Flag") = IIf(current.Month < Me.ProductHierarchyControl1.FCSTMonth, "ACT", "FCST")

                        If Not Me.ProductHierarchyControl1.SpecialMonth Then
                            If current.Month = Integer.Parse(Me.ProductHierarchyControl1.Month) Then
                                dr("Curr") = "true"
                            End If
                        End If

                        dt.Rows.Add(dr)
                    Next

                    '剩餘月份
                    Dim remainMons As Integer = 15 - currMons

                    firstMonth = New Date(Me.ProductHierarchyControl1.Year + 1, 1, 1)
                    For i As Integer = 0 To IIf(remainMons > 12, 2, 1) * 12 - 1
                        Dim dr As DataRow = dt.NewRow
                        Dim current As Date = firstMonth.AddMonths(i)

                        dr("Month") = current
                        dr("Flag") = "FCST"

                        dt.Rows.Add(dr)
                    Next
                Else
                    'FCST
                    firstMonth = New Date(Me.ProductHierarchyControl1.Year + Me.ProductHierarchyControl1.DisplayYear, 1, 1)
                    For i As Integer = 0 To 12 - 1
                        Dim dr As DataRow = dt.NewRow
                        Dim current As Date = firstMonth.AddMonths(i)

                        dr("Month") = current

                        If current.Year = Me.ProductHierarchyControl1.Year Then
                            If Not Me.ProductHierarchyControl1.SpecialMonth AndAlso current.Month = Integer.Parse(Me.ProductHierarchyControl1.Month) Then
                                dr("Curr") = "true"
                            End If
                            dr("Flag") = IIf(current.Month < Me.ProductHierarchyControl1.FCSTMonth, "ACT", "FCST")
                        Else
                            dr("Flag") = "FCST"
                        End If
                        dt.Rows.Add(dr)
                    Next
                End If

                Dim c As New DataColumn

                c.ColumnName = "yy"
                c.DataType = GetType(Integer)
                dt.Columns.Add(c)

                For Each dr As DataRow In dt.Rows
                    dr("yy") = DirectCast(dr("Month"), Date).Year
                Next
                'c.Expression = "year(month)"

                _MonthData = dt
            End If

            _MonthData.PrimaryKey = New DataColumn() {_MonthData.Columns("Month")}
            Return _MonthData
        End Get
    End Property

    Public ReadOnly Property TTLMonthFormula() As String
        Get
            Dim formulas As New List(Of String)

            For i As Integer = 1 To 12
                formulas.Add(String.Format("RC[-{0}]", i))
            Next

            Return String.Join("+", formulas.ToArray)
        End Get
    End Property

#End Region

#Region "屬性"
    Public ReadOnly Property Division() As String
        Get
            Return Me.Request.QueryString.Item("Division")
        End Get
    End Property

    Public ReadOnly Property ViewPoint() As String
        Get
            Return "SO_SF"
        End Get
    End Property

    Public ReadOnly Property HeaderTB() As String
        Get
            Return Me.ViewPoint.Replace("SF", "")
        End Get
    End Property

    Public Property ChannelPercentage() As DataTable
        Get
            Return Session(Me.SessionID & "ChannelPercentage")
        End Get
        Set(ByVal value As DataTable)
            Session(Me.SessionID & "ChannelPercentage") = value
        End Set
    End Property

    Public ReadOnly Property SearchOption() As SearchOptionControl
        Get
            Return Me.ProductHierarchyControl1
        End Get
    End Property

    Public Property CatagoryString() As String
        Get
            Return ViewState("CatagoryString")
        End Get
        Set(ByVal value As String)
            ViewState("CatagoryString") = value
        End Set
    End Property

    Protected ReadOnly Property ColumnSection() As String
        Get
            If Request("ColumnSection") Is Nothing Then
                If Me.Search2.ReportMode = ReportModeE.Gross Then
                    Return "Gross"
                Else
                    Return "Unit"
                End If
            End If

            Return Request("ColumnSection")
        End Get
    End Property

    Public WriteOnly Property ReportMode() As ReportModeE
        Set(ByVal value As ReportModeE)
            Select Case value
                Case ReportModeE.Meeting
                    Me.ProductHierarchyControl1.Visible = False
            End Select

            Me.Search2.ReportMode = value
        End Set
    End Property

    '判斷目前是否為Meeting的單一Summary
    Public ReadOnly Property MeetingSummary() As Boolean
        Get
            If Me.Request.QueryString("MeetingSummary") IsNot Nothing Then
                If Me.Request.QueryString("MeetingSummary").ToString() = "Y" Then
                    Return True
                Else
                    Return False
                End If
            Else
                If Me.Search2.ReportMode = ReportModeE.Meeting Then
                    Return True
                Else
                    Return False
                End If
            End If
        End Get
    End Property

    Public Property SessionID() As String
        Get
            If ViewState("SessionID") Is Nothing Then
                If Me.Request.QueryString.Item("SessionID") IsNot Nothing Then
                    ViewState("SessionID") = Me.Request.QueryString.Item("SessionID").ToString()
                Else
                    Dim MyRandom As New clsRandom
                    ViewState("SessionID") = MyRandom.Generate(10)
                End If
            End If

            Return ViewState("SessionID")
        End Get
        Set(ByVal value As String)
            ViewState("SessionID") = value
        End Set
    End Property

    Public Property SFModel() As SO
        Get
            If Session(Me.SessionID & "SFModel") Is Nothing Then
                Me.SFModel = New SO
            End If
            Return CType(Session(Me.SessionID & "SFModel"), SO)
        End Get
        Set(ByVal value As SO)
            Session(Me.SessionID & "SFModel") = value
        End Set
    End Property

    Public Property VControl() As Integer
        Get
            Return ViewState("VControl")
        End Get
        Set(ByVal value As Integer)
            ViewState("VControl") = value
        End Set
    End Property
    Public Property ProgramID() As String
        Get
            Return ViewState("ProgramID")
        End Get
        Set(ByVal value As String)
            ViewState("ProgramID") = value
        End Set
    End Property
    Public Property ProgramParent() As Integer
        Get
            Return ViewState("ProgramParent")
        End Get
        Set(ByVal value As Integer)
            ViewState("ProgramParent") = value
        End Set
    End Property

    Public Property DefaultReportMode() As Integer
        Get
            Return ViewState("DefaultReportMode")
        End Get
        Set(ByVal value As Integer)
            ViewState("DefaultReportMode") = value
        End Set
    End Property

    Public Property ReportXML() As String
        Get
            'Return Session(Me.SessionID)
            Return Me.ResolveUrl(MyObj.GetXmlUrl(Me.SessionID))
        End Get
        Set(ByVal value As String)
            'MyObj.WriteLog(Me.SessionID)
            Session(Me.SessionID) = value
        End Set
    End Property
#End Region

End Class

#Region "SO模組"
Public Class SO
    Protected stManager As New SignatureManager
    Protected sfmanager As New SFSapcodeManager
    Protected cmanager As New ChannelManager
    Protected lvlmanager As New LevelManager

#Region "方法"
    Public Sub InitData()
        If Me.ReportXML.Count > 0 Then
            Me.ReportXML.Clear()
        End If
    End Sub

    Public Sub BindData()
        Me._SapCodeData = Nothing
        Me._ChlData = Nothing
        Me._SigData = Nothing
        Me.UserSelectedLevels.Clear()
        Me.SetCatagoryAuth()
        Me.SetMemo()
        Me.BindXML()
    End Sub

    Public Function GetXMLDataOne() As String
        Me.BindData()
        Return Me.GenerateXMLData(1)
    End Function

    Public Function GetXMLDataAll() As String
        Return Me.GenerateXMLData(Me.ReportXML.Count)
    End Function

    Private Function GenerateXMLData(ByVal index As Integer) As String
        Dim XMLData As New StringBuilder

        'Top
        Dim vTop As New MyVelocity("SF-Top.xml")
        vTop.Put("S1", "##")
        vTop.Put("ProdLevelData", Me.dtProdLevel)
        XMLData.Append(vTop.Output())
        vTop = Nothing

        'Main
        For i As Integer = 0 To index - 1
            XMLData.Append(Me.ReportXML(i))
            Me.ReportXML(i) = Nothing
            GC.Collect()
            GC.WaitForPendingFinalizers()
        Next
        Me.ReportXML.Clear()
        GC.Collect()
        GC.WaitForPendingFinalizers()

        'Down
        Dim vDown As New MyVelocity("SFV2-Down.xml")
        XMLData.Append(vDown.Output())
        vDown = Nothing

        Return XMLData.ToString()
    End Function

    Dim rowCount As Integer = 0
    Dim CustomFormat As List(Of String)
    Dim TotalRowCount As Integer
    Protected Sub BindXML()
        Dim v As New MyVelocity("SO-SF-Main.xml")
        v.Put("SheetName", Me.SF_SheetName)
        Dim MyVersion As String = ""
        Dim StartIndex As Integer = Me.SF_SheetName.IndexOf(".")
        MyVersion = Me.SF_SheetName.Substring(StartIndex + 1, Me.SF_SheetName.Length - StartIndex - 1)
        v.Put("Signature", MyVersion)
        v.Put("yyData", Me.yyData)
        v.Put("MonthData", Me.MonthData)
        v.Put("Page", Me)
        v.Put("ColumnSection", Me.ColumnSection)
        Me.CustomFormat = New List(Of String)
        Me.TotalRowCount = 4 '前四列
        Me.ReportXML.Add(v.Output().Replace("${RowCount}", Me.rowCount * 11 + 20).Replace("&", "&amp;").Replace("_%_", "%").Replace("${CustomFormat}", Me.GetCustomFormat("Decimal", "0", "1")))
        System.GC.Collect()
        System.GC.WaitForPendingFinalizers()
    End Sub

    '取得Memo資料
    Private Sub SetMemo()
        Dim ntmanager As New NotesManager
        ntmanager.ViewPoint = Me.ViewPoint
        Dim dtMemo As DataTable = ntmanager.GetAllSFNotes(Me.SF_MonthCode, Me.SF_Channel)
        dtMemo.PrimaryKey = New DataColumn() {dtMemo.Columns("MonthCode"), dtMemo.Columns("SkuType"), dtMemo.Columns("SapCode"), dtMemo.Columns("MonIndex")}
        Me.ReportMemo = dtMemo
    End Sub

    '設定新分類的權限
    Public Sub SetCatagoryAuth()
        Dim MyCatagoryString As New StringBuilder
        If Me.SF_IsCatagory Then
            Dim lManager As New LevelManager
            Dim MyRow As DataRow = Me.lvlmanager.GetLevelName2(Me.SF_Division, Me.ViewPoint, 2)
            If MyRow IsNot Nothing Then
                Dim ItemCodes As String = MyObj.JoinFormat(",", "'{0}'", lManager.GetItemCodes(Me.SF_Division, MyRow("LevelName"), Me.SF_SignatureText))

                If ItemCodes <> "" Then
                    If MyRow("Chain") = "Y" Then
                        MyCatagoryString.AppendFormat("AND {0} in ", String.Format("substring(Hierarchy,1,{0}*2)", MyRow("Sequence")))
                    Else
                        MyCatagoryString.AppendFormat("AND {0} in ", String.Format("substring(Hierarchy,{0}*2-1,2)", MyRow("Sequence")))
                    End If
                    MyCatagoryString.AppendFormat("({0}) ", ItemCodes)
                End If
            End If

            MyRow = Me.lvlmanager.GetLevelName2(Me.SF_Division, Me.ViewPoint, 3)
            If MyRow IsNot Nothing Then
                Dim ItemCodes As String = MyObj.JoinFormat(",", "'{0}'", lManager.GetItemCodes(Me.SF_Division, MyRow("LevelName"), Me.SF_SignatureText))

                If ItemCodes <> "" Then
                    If MyRow("Chain") = "Y" Then
                        MyCatagoryString.AppendFormat("AND {0} in ", String.Format("substring(Hierarchy,1,{0}*2)", MyRow("Sequence")))
                    Else
                        MyCatagoryString.AppendFormat("AND {0} in ", String.Format("substring(Hierarchy,{0}*2-1,2)", MyRow("Sequence")))
                    End If
                    MyCatagoryString.AppendFormat("({0}) ", ItemCodes)
                End If
            End If
        End If
        Me.SF_CatagoryString = MyCatagoryString.ToString()
        'MyObj.WriteLog(Me.SF_CatagoryString)
    End Sub

    Public Function GetCustomFormat(ByVal Type As String, ByVal Min As String, ByVal Max As String) As String
        If Me.CustomFormat.Count = 0 Then
            Return ""
        Else
            Dim SepCount As Integer = 20 '每N個一組
            Dim sb As New StringBuilder
            For i As Integer = 0 To Me.CustomFormat.Count - 1
                If (i + 1) Mod SepCount = 1 Then
                    sb.AppendFormat("<DataValidation xmlns=""urn:schemas-microsoft-com:office:excel"">")
                    sb.AppendFormat("<Range>R1C1")
                End If

                sb.AppendFormat(",{0}", Me.CustomFormat(i))

                If (i + 1) Mod SepCount = 0 OrElse i = Me.CustomFormat.Count - 1 Then
                    sb.AppendFormat("</Range>")
                    sb.AppendFormat("<Type>{0}</Type>", Type)
                    sb.AppendFormat("<Min>{0}</Min>", Min)
                    sb.AppendFormat("<Max>{0}</Max>", Max)
                    sb.AppendFormat("<InputHide/>")
                    sb.AppendFormat("</DataValidation>")
                End If
            Next
            Return sb.ToString()
        End If
    End Function
#End Region

#Region "主要運算邏輯區塊"

    '各階層
    Dim dtProdHierarchy As DataTable
    '18碼
    Dim dtProd As DataTable
    'SKU
    Dim dtProd2 As DataTable
    'All SapCode
    Dim dtAllSapCode As DataTable
    'SFReport FCST
    Dim dtReport As DataTable
    'SFReport FCST Memo
    Dim dtReportMemo As DataTable

    'SFReport ACT Y, Y-1, Y-2
    Dim dtACTs(2) As DataTable
    'SFReport ACTPBOM Y, Y-1, Y-2
    Dim dtACTPBOM(2) As DataTable

    'BOM
    Dim dtBOM As DataTable
    '
    Dim _dtProdLevel As DataTable
    Dim _UserSelectedLevels As New List(Of UserSelectedLevel)
    '最後一層已顯示數量 (顯示 SKU)
    Dim currentProdCount As Integer = 0
    Dim mSapCodeDt As New DataTable
    'used by velocity
    Public Function RenderContent() As String
        'MyObj.WriteLog("Query_Start:" & Date.Now.ToString())
        Dim dat As New clsData
        Dim sql As New StringBuilder
        Dim sqlVersion As New StringBuilder
        Dim sqltemplate As New StringBuilder
        Dim sqlProd As New StringBuilder
        Dim where As New StringBuilder
        Dim where2 As New StringBuilder
        Dim where3 As New StringBuilder
        Dim where4 As New StringBuilder
        Dim unions As New List(Of List(Of String))
        Dim params As New Dictionary(Of String, Object)

        Dim AuthSql As New StringBuilder
        AuthSql.AppendFormat("select AuthProdHierarchy into #tempA ")
        AuthSql.AppendFormat("from EmployeeHierarchyMapping where EmployeeID='{0}' ", clsAuth.GetUserID())
        AuthSql.AppendFormat("and ViewPoint='{0}' and Division='{1}'; ", Me.ViewPoint, Me.SF_Division)

        'Meeting模組只取GrossSales排名前20筆的Sku
        Me.mSapCodeDt = New DataTable

        If Me.MeetingSummary Then
            Dim sf As New SFSapCode
            sf.MonthCode = Me.SF_MonthCode
            sf.ViewPoint = Me.ViewPoint
            sf.Division = Me.SF_Division
            sf.Signature = Me.SF_BasicSignature
            sf.ChannelID = Me.SF_Channel
            sf.MMPP = Me.SF_MMPP
            sf.ReportMode = ReportModeE.Meeting
            sf.Month = Me.SF_Month
            sf.UserSelectedLevelString = Me.UserSelectedLevelString
            mSapCodeDt = sfmanager.GetSF_SapCode(sf)
        End If

        '取出該版本的所有SapCode,用於ACT判斷
        sqltemplate.Length = 0
        sqltemplate.AppendFormat("Select A.SapCode from SO_SFReport{0} A ", Me.SF_MonthCode)
        sqltemplate.AppendFormat("join ProductData B on A.SapCode=B.SapCode ")
        sqltemplate.AppendFormat("And left(B.ProdHierarchy,2) in ({0}) ", MyObj.JoinFormat(",", "'{0}'", Me.SigData))
        sqltemplate.AppendFormat("where A.skutype='S' and A.Channelid='' and A.Version1='M' ")
        Me.dtAllSapCode = dat.GetData(sqltemplate.ToString())
        Me.dtAllSapCode.PrimaryKey = New DataColumn() {Me.dtAllSapCode.Columns(0)}

        '使用該版本的ProdHierarchy
        sqlVersion.AppendFormat("join (select distinct pd.ProdHierarchy as VProdHierarchy from SO_SFReport{0} SF join ", Me.SF_MonthCode)
        sqlVersion.AppendFormat("ProductData pd on SF.SapCode=pd.SapCode and SF.Version1='M' and SF.Skutype='S' and ChannelID='' ")
        If Me.MeetingSummary Then
            If mSapCodeDt.Rows.Count > 0 Then
                For j As Integer = 0 To mSapCodeDt.Rows.Count - 1
                    If j = 0 Then
                        sqlVersion.AppendFormat("AND SF.SapCode in('{0}'", mSapCodeDt.Rows(j)("SapCode"))
                    Else
                        sqlVersion.AppendFormat(",'{0}'", mSapCodeDt.Rows(j)("SapCode"))
                    End If
                    If j = mSapCodeDt.Rows.Count - 1 Then
                        sqlVersion.AppendFormat(") ")
                    End If
                Next
            Else
                sqlVersion.AppendFormat("AND 1=2 ")
            End If
        End If
        sqlVersion.AppendFormat(") V on V.VProdHierarchy=EHM.AuthProdHierarchy ")


        '畫面的Hierarchy層級
        sqltemplate.Length = 0
        sqltemplate.Append("SELECT {0}, description,Code as AuthProdHierarchy ")
        sqltemplate.AppendFormat("FROM ProductHierarchyOriginal ")
        sqltemplate.AppendFormat("WHERE 1=1 ")
        sqltemplate.AppendFormat("And left(code,2) in ({0}) ", MyObj.JoinFormat(",", "'{0}'", Me.SigData))

        'MyObj.WriteLog("u.LevelName:" & Me.ProductHierarchyControl1.SecondLevelName)
        'MyObj.WriteLog("ProductHierarchyControl1.IsCatagory:" & ProductHierarchyControl1.IsCatagory)

        For Each u As UserSelectedLevel In Me.UserSelectedLevels
            Dim targetcol As String = ""
            Dim setting As DataRow = Me.Level(u.LevelName)
            Dim GetSequence As DataRow = Me.Level(Me.SF_SecondLevelName)
            Dim union As New List(Of String)

            If setting("Chain") = "Y" Then
                targetcol = String.Format("substring(code,1,{0}*2)", GetSequence("Sequence"))
                union.Add(String.Format("substring(code,1,{0}*2) as Code, '{1}' AS LevelName ", setting("Sequence"), u.LevelName))
            Else
                targetcol = String.Format("substring(code,{0}*2-1,2)", GetSequence("Sequence"))
                union.Add(String.Format("substring(code,{0}*2-1,2) as Code, '{1}' AS LevelName ", setting("Sequence"), u.LevelName))
            End If

            union.Add(String.Format("AND len(code) = {0}*2 and Levelname='{1}'", setting("sequence"), u.LevelName))
            unions.Add(union)

            If u.LevelValue = String.Empty Then Continue For

            If u.LevelName = "Signature" Then
                where.AppendFormat("AND substring(code,1,2) in ({0}) ", MyObj.JoinFormat(",", "'{0}'", Me.SigData))
            Else
                If setting("Chain") = "Y" Then
                    where.AppendFormat("AND substring(code,1,{0}*2) = '{1}' ", setting("Sequence"), u.LevelValue)
                Else
                    where.AppendFormat("AND substring(code,{0}*2-1,2) = '{1}' ", setting("Sequence"), u.LevelValue)
                End If
            End If
        Next

        '新分類必需加上額外條件
        where.Append(Me.SF_CatagoryString.Replace("Hierarchy", "code"))

        If Me.SF_MMPP = "AllPLV" Then
            where2.AppendFormat("AND sf.MaterialType not in('N1','B1','R1','B2') ")
        ElseIf Me.SF_MMPP = "AllFG" Then
            where2.AppendFormat("AND sf.MaterialType in('N1','R1') ")
        Else
            where2.AppendFormat("AND sf.MaterialType='{0}' ", Me.SF_MMPP)
        End If

        '只取有權限管理的Channel資料,Add By Angus 2010/09/16
        If Me.ChannelPercentage IsNot Nothing Then
            For j As Integer = 0 To ChannelPercentage.Rows.Count - 1
                If j = 0 Then
                    where3.AppendFormat("AND sf.ChannelID in('{0}'", ChannelPercentage.Rows(j)("Channel"))
                Else
                    where3.AppendFormat(",'{0}'", ChannelPercentage.Rows(j)("Channel"))
                End If
                If j = ChannelPercentage.Rows.Count - 1 Then
                    where3.AppendFormat(") ")
                End If
            Next
        End If
        where3.AppendFormat("AND sf.ChannelID in({0}) ", MyObj.JoinFormat(",", "'{0}'", Me.ChlData))

        Dim real_sql As New StringBuilder
        'Update By Angus 20101122
        real_sql.AppendFormat(AuthSql.ToString())

        real_sql.AppendFormat("Select DISTINCT P.code,P.LevelName,'('+Right(P.code,2)+')'+P.description as description,Cast(PL.Sequence as varchar)+'G' as LevelName2 from ")
        real_sql.AppendFormat("( ")
        real_sql.AppendFormat(" Select code,LevelName,description,null as AuthProdHierarchy from ProductHierarchyOriginal where 1=2 ")
        For Each union As List(Of String) In unions
            real_sql.AppendFormat("union " + String.Format(sqltemplate.ToString, union(0)) + union(1) + " ")
        Next
        real_sql.AppendFormat(") P ")
        real_sql.AppendFormat("join Productlevel PL on P.LevelName=PL.LevelName ")
        real_sql.AppendFormat("join #tempA EHM on P.AuthProdHierarchy=left(EHM.AuthProdHierarchy,len(P.AuthProdHierarchy)) ")
        real_sql.AppendFormat("And left(EHM.AuthProdHierarchy,2) in ({0}) ", MyObj.JoinFormat(",", "'{0}'", Me.SigData))
        real_sql.AppendFormat(sqlVersion.ToString())
        real_sql.AppendFormat("Order by P.Code ")
        real_sql.AppendFormat(";Drop table #TempA ")

        'MyObj.WriteLog(real_sql.ToString())
        dtProdHierarchy = dat.GetData(real_sql.ToString(), params)
        dtProdHierarchy.DefaultView.RowFilter = "1=1"
        dtProdHierarchy.PrimaryKey = New DataColumn() {dtProdHierarchy.Columns("LevelName"), dtProdHierarchy.Columns("Code")}

        If Me.SF_IsCatagory Then
            For Each row As DataRow In dtProdHierarchy.Select("LevelName='Signature'")
                row("Description") = Me.SF_SignatureText
            Next
        End If

        'SFReport
        Dim sqlReport As New StringBuilder
        Dim mons As New List(Of String)

        params.Clear()
        sqlReport.Length = 0
        sqlReport.AppendFormat(AuthSql.ToString())

        sqlReport.AppendFormat("SELECT sf.*,p.CurrCode,p.OldCode,p2.SapCode as OldCode2, p.Description , p.LocalDescription, p.ProdHierarchy as code,p.MMPP ")
        sqlReport.AppendFormat("FROM (")

        If Me.IsMutiChannel Then 'All Channel
            For i As Integer = 1 To 36
                mons.Add(i)
            Next
            sqlReport.AppendFormat("SELECT SapCode, SkuType,MaterialType, {0}  ", MyObj.JoinFormat(",", "SUM(ISNULL(Mon{0},0)) AS Mon{0}", mons))
            sqlReport.AppendFormat("FROM {0} sf WHERE Version1='S' AND SkuType IN ('Piping','SellOut') " + where2.ToString() + where3.ToString() + " GROUP BY SapCode, SkuType, MaterialType ", Me.ReportName)

            Dim vcManager As New VersionManager(DateTime.Now.ToString("yyyyMM"), Me.SF_Signature, Me.HeaderTB)
            If Not (Me.SF_MonthCode = DateTime.Now.AddMonths(1).ToString("yyyyMM") AndAlso vcManager.Generated = False) Then
                sqlReport.AppendFormat("UNION SELECT SapCode, SkuType,MaterialType, {0} ", MyObj.JoinFormat(",", "ISNULL(SUM(Mon{0}),0)", mons))
                sqlReport.AppendFormat("FROM {0} sf WHERE Version1='M' AND SkuType='ListP' and ChannelID='' " + where2.ToString(), Me.OldReportName)
                sqlReport.AppendFormat("and SapCode in(Select SapCode from {0} where Version1='M' and ChannelID='') ", ReportName)
                sqlReport.AppendFormat("GROUP BY SapCode, SkuType, MaterialType ")
            End If
        Else
            sqlReport.AppendFormat(" SELECT * FROM {0} sf WHERE Version1='S' AND SkuType IN ('Piping','SellOut') " + where2.ToString() + " ", ReportName)
            sqlReport.AppendFormat(" AND ChannelID=@ChannelID ")
            params.Add("ChannelID", Me.SF_Channel)

            Dim vcManager As New VersionManager(DateTime.Now.ToString("yyyyMM"), Me.SF_Signature, Me.HeaderTB)
            If Not (Me.SF_MonthCode = DateTime.Now.AddMonths(1).ToString("yyyyMM") AndAlso vcManager.Generated = False) Then
                sqlReport.AppendFormat(" UNION SELECT * FROM {0} sf WHERE Version1='M' AND SkuType='ListP' " + where2.ToString() + " ", Me.OldReportName)
                sqlReport.AppendFormat(" and SapCode in(Select SapCode from {0} where Version1='M' and ChannelID='') ", ReportName)
            End If
        End If

        sqlReport.AppendFormat(") sf ")
        sqlReport.AppendFormat("INNER JOIN ProductData p ON sf.SapCode = p.SapCode ")
        sqlReport.AppendFormat("join #tempA EHM on P.ProdHierarchy=EHM.AuthProdHierarchy ")
        sqlReport.AppendFormat("And left(EHM.AuthProdHierarchy,2) in ({0}) ", MyObj.JoinFormat(",", "'{0}'", Me.SigData))
        sqlReport.AppendFormat("LEFT JOIN (select SapCode,FollowCode,OldCode from ProductData) p2 ON isnull(p.OldCode,'')<>'' and p.OldCode = p2.FollowCode ")
        sqlReport.AppendFormat("WHERE 1=1 ")
        sqlReport.AppendFormat(where.ToString.Replace("code", "ProdHierarchy"))

        If Me.ShowOneSummary = True Then
            sqlReport.AppendFormat("AND p.MMPP NOT IN ('07','08','09') ")
        End If

        If Me.MeetingSummary = True Then
            If mSapCodeDt.Rows.Count > 0 Then
                Dim OrderString As String = "Order by Case SF.SapCode "
                For j As Integer = 0 To mSapCodeDt.Rows.Count - 1
                    If j = 0 Then
                        sqlReport.AppendFormat("AND SF.SapCode in('{0}'", mSapCodeDt.Rows(j)("SapCode"))
                    Else
                        sqlReport.AppendFormat(",'{0}'", mSapCodeDt.Rows(j)("SapCode"))
                    End If
                    If j = mSapCodeDt.Rows.Count - 1 Then
                        sqlReport.AppendFormat(") ")
                    End If
                    OrderString &= String.Format("when '{0}' then {1} ", mSapCodeDt.Rows(j)("SapCode"), j)
                Next
                OrderString &= "End "
                sqlReport.AppendFormat(OrderString)
            Else
                sqlReport.AppendFormat("AND 1=2 ")
            End If
        Else
            sqlReport.AppendFormat("Order by CurrCode ")
        End If
        real_sql.AppendFormat(";Drop table #TempA ")
        'MyObj.WriteLog(sqlReport.ToString())
        dtReport = dat.GetData(sqlReport.ToString, params)

        '取代單引號、雙引號
        For i As Integer = 0 To dtReport.Rows.Count - 1
            dtReport.Rows(i)("Description") = dtReport.Rows(i)("Description").ToString().Replace("'", "’").Replace("""", "''")
            dtReport.Rows(i)("LocalDescription") = dtReport.Rows(i)("LocalDescription").ToString().Replace("'", "’").Replace("""", "''")
        Next

        'Response.End()
        '取出所有要用的SapCode
        Dim SapCodeDT As New DataTable
        Dim TempDT As New DataTable
        SapCodeDT = dtReport.DefaultView.ToTable(True, "SapCode") 'True代表要distinct
        Me.SapCodeDT = SapCodeDT
        TempDT = dtReport.DefaultView.ToTable(True, "OldCode")
        TempDT.Columns(0).ColumnName = "SapCode"
        SapCodeDT.Merge(TempDT)
        TempDT = dtReport.DefaultView.ToTable(True, "OldCode2")
        TempDT.Columns(0).ColumnName = "SapCode"
        SapCodeDT.Merge(TempDT)

        dtReport.PrimaryKey = New DataColumn() {dtReport.Columns("SapCode"), dtReport.Columns("SkuType")}
        '取得SKU資料
        dtProd2 = dtReport.DefaultView.ToTable(True, "SapCode", "CurrCode", "OldCode", "OldCode2", "Description", "LocalDescription", "code", "MMPP")
        dtProd2.PrimaryKey = New DataColumn() {dtProd2.Columns("SapCode")}

        '取得18碼資料
        dtProd = dtProd2.DefaultView.ToTable(True, "code")
        dtProd.DefaultView.RowFilter = "1=1"
        dtProd.PrimaryKey = New DataColumn() {dtProd.Columns("Code")}

        mons.Clear()
        For i As Integer = 1 To 12
            mons.Add(i)
        Next
        '取得ACT資料
        For i As Integer = 0 To dtACTs.Length - 1
            params.Clear()
            sqlReport.Length = 0
            sqlReport.AppendFormat(AuthSql.ToString())
            If Me.IsMutiChannel Then 'All Channel
                sqlReport.AppendFormat("SELECT sf.SapCode,case sf.SkuType when 'S' then 'Piping' else sf.SkuType end as SkuType,sf.MaterialType, {0} ", MyObj.JoinFormat(",", "SUM(ISNULL(Mon{0},0)) AS Mon{0}", mons))
                sqlReport.AppendFormat("FROM {0} sf ", ACTReportName(-i))
                sqlReport.AppendFormat("INNER JOIN ProductData p ON sf.SapCode = p.SapCode ")
                sqlReport.AppendFormat("join #tempA EHM on P.ProdHierarchy=EHM.AuthProdHierarchy ")
                sqlReport.AppendFormat("And left(EHM.AuthProdHierarchy,2) in ({0}) ", MyObj.JoinFormat(",", "'{0}'", Me.SigData))
                sqlReport.AppendFormat("WHERE 1=1 ")
                sqlReport.AppendFormat("And (ChannelID in ({0}) or ChannelID='') ", MyObj.JoinFormat(",", "'{0}'", Me.ChlData))
            Else
                sqlReport.AppendFormat("SELECT sf.SapCode,case sf.SkuType when 'S' then 'Piping' else sf.SkuType end as SkuType,sf.MaterialType, {0} ", MyObj.JoinFormat(",", "ISNULL(Mon{0},0) AS Mon{0}", mons))
                sqlReport.AppendFormat("FROM {0} sf ", ACTReportName(-i))
                sqlReport.AppendFormat("INNER JOIN ProductData p ON sf.SapCode = p.SapCode ")
                sqlReport.AppendFormat("join #tempA EHM on P.ProdHierarchy=EHM.AuthProdHierarchy ")
                sqlReport.AppendFormat("And left(EHM.AuthProdHierarchy,2) in ({0}) ", MyObj.JoinFormat(",", "'{0}'", Me.SigData))
                sqlReport.AppendFormat("WHERE 1=1 ")
                sqlReport.AppendFormat(" AND (ChannelID=@ChannelID or (Skutype in('ListP','STD') and ChannelID='')) ")
                params.Add("ChannelID", Me.SF_Channel)
            End If
            If SapCodeDT.Rows.Count > 0 Then
                For j As Integer = 0 To SapCodeDT.Rows.Count - 1
                    If j = 0 Then
                        sqlReport.AppendFormat("AND sf.SapCode in('{0}'", SapCodeDT.Rows(j)("SapCode"))
                    Else
                        sqlReport.AppendFormat(",'{0}'", SapCodeDT.Rows(j)("SapCode"))
                    End If
                    If j = SapCodeDT.Rows.Count - 1 Then
                        sqlReport.AppendFormat(")")
                    End If
                Next
            Else
                sqlReport.AppendFormat("AND 1=2 ")
            End If
            If Me.IsMutiChannel Then 'All Channel
                sqlReport.AppendFormat("Group by sf.SapCode, sf.SkuType,sf.MaterialType ")
            End If
            sqlReport.AppendFormat(";Drop table #TempA ")

            dtACTs(i) = dat.GetData(sqlReport.ToString, params)
            dtACTs(i).PrimaryKey = New DataColumn() {dtACTs(i).Columns("SapCode"), dtACTs(i).Columns("SkuType")}
        Next

        '取得ACT SellOutPBOM資料
        For i As Integer = 0 To dtACTPBOM.Length - 1
            sqlReport.Length = 0
            sqlReport.AppendFormat(AuthSql.ToString())
            sqlReport.AppendFormat("SELECT bom.BOMSapCode, bom.Qty, {0} ", MyObj.JoinFormat(",", "ISNULL(Mon{0},0) AS Mon{0}", mons))
            sqlReport.AppendFormat("FROM {0} bom ", BOMTableName)
            sqlReport.AppendFormat("INNER JOIN ProductData p ON p.SapCode = bom.BOMSapCode ")
            sqlReport.AppendFormat("join #TempA EHM on P.ProdHierarchy=EHM.AuthProdHierarchy ")
            sqlReport.AppendFormat("And left(EHM.AuthProdHierarchy,2) in ({0}) ", MyObj.JoinFormat(",", "'{0}'", Me.SigData))
            sqlReport.AppendFormat("INNER JOIN {0} sf ON sf.SapCode = bom.SapCode ", ACTReportName(-i))
            sqlReport.AppendFormat(" AND sf.SkuType IN ('SellOut') ")

            sqlReport.AppendFormat("WHERE 1=1 ")
            sqlReport.AppendFormat("And sf.ChannelID in ({0}) ", MyObj.JoinFormat(",", "'{0}'", Me.ChlData))

            If Me.ShowOneSummary = True Then
                sqlReport.AppendFormat("AND p.MMPP NOT IN ('07','08','09') ")
            End If

            If SapCodeDT.Rows.Count > 0 Then
                For j As Integer = 0 To SapCodeDT.Rows.Count - 1
                    If j = 0 Then
                        sqlReport.AppendFormat("AND bom.BOMSapCode in('{0}'", SapCodeDT.Rows(j)("SapCode"))
                    Else
                        sqlReport.AppendFormat(",'{0}'", SapCodeDT.Rows(j)("SapCode"))
                    End If
                    If j = SapCodeDT.Rows.Count - 1 Then
                        sqlReport.AppendFormat(")")
                    End If
                Next
            Else
                sqlReport.AppendFormat("AND 1=2 ")
            End If
            sqlReport.AppendFormat(";Drop table #TempA ")

            'MyObj.WriteLog(sqlReport.ToString())
            dtACTPBOM(i) = dat.GetData(sqlReport.ToString())
        Next

        '取得BOM資料
        sqlReport.Length = 0
        sqlReport.AppendFormat(AuthSql.ToString())
        sqlReport.AppendFormat("SELECT sf.*, bom.BOMSapCode, bom.Qty ")
        sqlReport.AppendFormat("FROM {0} bom ", BOMTableName)
        sqlReport.AppendFormat("INNER JOIN ProductData p ON p.SapCode = bom.BOMSapCode ")
        sqlReport.AppendFormat("join #TempA EHM on P.ProdHierarchy=EHM.AuthProdHierarchy ")
        sqlReport.AppendFormat("And left(EHM.AuthProdHierarchy,2) in ({0}) ", MyObj.JoinFormat(",", "'{0}'", Me.SigData))
        'sqlReport.AppendFormat(sqlVersion.ToString())
        sqlReport.AppendFormat("INNER JOIN {0} sf ON sf.SapCode = bom.SapCode AND sf.SkuType IN ('Piping','SellOut') AND sf.Version1 = @Version1 ", Me.ReportName)
        sqlReport.AppendFormat("WHERE 1=1 ")
        sqlReport.AppendFormat("And sf.ChannelID in ({0}) ", MyObj.JoinFormat(",", "'{0}'", Me.ChlData))

        If SapCodeDT.Rows.Count > 0 Then
            For j As Integer = 0 To SapCodeDT.Rows.Count - 1
                If j = 0 Then
                    sqlReport.AppendFormat("AND bom.BOMSapCode in('{0}'", SapCodeDT.Rows(j)("SapCode"))
                Else
                    sqlReport.AppendFormat(",'{0}'", SapCodeDT.Rows(j)("SapCode"))
                End If
                If j = SapCodeDT.Rows.Count - 1 Then
                    sqlReport.AppendFormat(")")
                End If
            Next
        Else
            sqlReport.AppendFormat("AND 1=2 ")
        End If
        sqlReport.AppendFormat(";Drop table #TempA ")

        params.Clear()
        params.Add("Version1", "S") 'Me.ProductHierarchyControl1.Version1
        'MyObj.WriteLog(sqlReport.ToString)
        dtBOM = dat.GetData(sqlReport.ToString, params)

        'Clear String
        sql.Length = 0
        sqlVersion.Length = 0
        sqltemplate.Length = 0
        sqlProd.Length = 0
        where.Length = 0
        where2.Length = 0
        where3.Length = 0
        where4.Length = 0
        unions.Clear()
        params.Clear()

        'Update By Angus 20101116
        'Reduce the product hierarchy information and only display "summary level" which selected by User
        'Dim s As New StringBuilder
        Dim SummaryIndex As Integer = 0
        Dim SummaryLevelName As String = "Signature"
        If Me.ShowOneSummary = True Then
            For i As Integer = Me.UserSelectedLevels.Count - 1 To 0 Step -1
                If Me.UserSelectedLevels(i).LevelValue <> "" Then
                    SummaryIndex = i
                    SummaryLevelName = Me.UserSelectedLevels(i).LevelName
                    Exit For
                End If
            Next
        End If
        'MyObj.WriteLog("Query_End:" & Date.Now.ToString())

        '設定產品資料
        Me.SetItemData()

        'Response.End()
        Dim MyRow As DataRow()
        If Not Me.IsMutiSignature Then
            MyRow = dtProdHierarchy.Select(String.Format("code='{0}' and LevelName='{1}'", Me.UserSelectedLevels(SummaryIndex).LevelValue, SummaryLevelName))
        Else
            MyRow = dtProdHierarchy.Select(String.Format("LevelName='{0}'", SummaryLevelName))
        End If
        'MyObj.WriteLog("MyRow.Length:" & MyRow.Length)

        If MyRow.Length > 0 Then
            'Meeting時只顯示Gross排行前20 Sku
            If Me.MeetingSummary = True Then
                's.AppendFormat(RenderItem(dtProd.DefaultView, MyRow(0), Me.UserSelectedLevels.Count - 1))
                Return RenderItem(dtProd.DefaultView, MyRow(0), Me.UserSelectedLevels.Count - 1)
            Else
                's.AppendFormat(RenderItem(dtProd.DefaultView, MyRow(0), 0))
                Return RenderItem(dtProd.DefaultView, MyRow(0), 0)
            End If
        Else
            Return ""
        End If
    End Function

    Dim TotalSkuCount As Integer = 0
    Dim AllString As New StringBuilder
    Dim vLevel As New MyVelocity("SO-SF-Level.xml")
    Dim vSku As New MyVelocity("SO-SF-SKU.xml")
    Dim vSku_Gross As New MyVelocity("SF-SKU-Gross.xml")
    Dim renderCount As Integer = 0
    Public Function RenderItem(ByVal dv As DataView, ByVal current As DataRow, ByVal showOrder As Integer) As String
        'Dim s As New StringBuilder
        Dim xmllist As New List(Of String)

        'Dim v As New MyVelocity
        Dim v As New MyVelocity()
        'v = Me.vLevel
        'MyObj.WriteLog("New MyVelocity_end:" & DateTime.Now.Second & ":" & DateTime.Now.Millisecond)

        Dim dv2 As New DataView(dtProd) 'Need Free

        Me.rowCount += 1
        Me.TotalRowCount += 9
        Dim myRowIndex As Integer = Me.rowCount
        Dim NowSkuCount As Integer = TotalSkuCount

        Dim levelName As String = Me.UserSelectedLevels(showOrder).LevelName
        'Dim levelSetting As DataRow = Me.Level(levelName) 

        Dim monthFormulaList As New List(Of String) 'Need Free
        v.Put("Current", current)
        v.Put("yyData", yyData)
        v.Put("MonthData", MonthData)
        PutMonthData(v)

        '第8階
        If (showOrder + 1 = Me.UserSelectedLevels.Count) OrElse (Me.ShowOneSummary = True And showOrder = 0) Then
            Dim codelist As New List(Of String) 'Need Free
            For Each row As DataRowView In dv
                codelist.Add(row("code").ToString())
            Next

            If Me.MeetingSummary Then
                Dim col As New DataColumn
                col = New DataColumn
                col.ColumnName = "GrossOrder"
                col.DataType = GetType(Integer)

                If Not dtProd2.Columns.Contains(col.ColumnName) Then
                    dtProd2.Columns.Add(col)
                End If
                For i As Integer = 0 To Me.mSapCodeDt.Rows.Count - 1
                    For ii As Integer = 0 To dtProd2.Rows.Count - 1
                        If mSapCodeDt.Rows(ii)("SapCode").ToString() = dtProd2.Rows(ii)("SapCode").ToString() Then
                            dtProd2.Rows(ii)(col.ColumnName) = i
                        End If
                    Next
                Next
            End If

            dv2 = New DataView(dtProd2)
            dv2.RowFilter = String.Format("code in ('{0}')", String.Join("','", codelist.ToArray))
            codelist.Clear()

            'Dim v2
            'If Search2.ReportMode = ReportModeE.Gross Then
            '    v2 = New MyVelocity("SF-SKU-Gross.xml")
            'Else
            '    v2 = New MyVelocity("SF-SKU.xml")
            'End If
            Dim v2 As New MyVelocity

            v2.Put("Page", Me)
            If Me.MeetingSummary Then
                '照Gross排序 Add By Angus 2011/11/2
                dv2.Sort = "GrossOrder desc"
            End If
            v2.Put("ProdData", dv2)
            v2.Put("AreaCode", MyObj.GetAreaCode())
            v2.Put("yyData", yyData)
            v2.Put("Coefficient", Me.Coefficient)

            If Me.SF_ReportMode = ReportModeE.Gross Then
                v2.Put("MonthData", MonthData)
            End If
            'v2.Put("BomData", dtBOM)
            'PutMonthData(v2)

            'MyObj.WriteLog("dv2.Count:" & dv2.Count)
            Me.rowCount += dv2.Count
            TotalSkuCount += dv2.Count
            For i As Integer = 1 To dv2.Count
                If i = 1 Then
                    monthFormulaList.Add(String.Format("R[{0}]C:R[{0}]C[11]", i * 9))
                Else
                    monthFormulaList.Add(String.Format("R[{0}]C:R[{0}]C[11]", (i - 1) * 11 + 9))
                End If
            Next

            v.Put("MonthFormula", String.Join("+", monthFormulaList.ToArray))
            monthFormulaList.Clear()

            'Last Lavel Use SumIF
            v.Put("LastLevel", "Y")
            v.Put("SumIFFormulaA", String.Format("SUMIF(R[{0}]C5:R[{1}]C5,""SkuType"",R[{0}]C:R[{1}]C)", 9, (dv2.Count - 1) * 11 + 9))
            v.Put("SumIFFormulaB", String.Format("SUM(IF(R[{0}]C5:R[{1}]C5=""SkuType"",IF(R[{0}]C6:R[{1}]C6=""Year"",R[{0}]C:R[{1}]C,0)))", 9, (dv2.Count - 1) * 11 + 9))

            'MyObj.WriteLog("OutPut_Start:" & Date.Now.ToString())
            AllString.Length = 0
            Dim skulist As New List(Of String)

            If Me.ShowLevel = True Then
                'MyObj.WriteLog("V1_Start:" & Date.Now.ToString())
                'AllString.Append(v.Output(Me.vLevel.Template))
                skulist.Add(v.Output(Me.vLevel.Template))
            End If
            'MyObj.WriteLog("V2_Start:" & Date.Now.ToString())

            'Meeting時只顯示Sku
            If Me.SF_ReportMode = ReportModeE.Gross Then
                'AllString.Append(v2.Output(Me.vSku_Gross.Template))
                skulist.Add(v2.Output(Me.vSku_Gross.Template))
            Else
                'AllString.Append(v2.Output(Me.vSku.Template))
                skulist.Add(v2.Output(Me.vSku.Template))
            End If
            Return String.Join("", skulist.ToArray())
        Else
            Dim MyRow As DataRow 'Need Free
            Dim nextLevelName As String = Me.UserSelectedLevels(showOrder + 1).LevelName
            'MyObj.WriteLog("nextLevelSetting_start:" & DateTime.Now.Second & ":" & DateTime.Now.Millisecond)
            Dim nextLevelSetting As DataRow = Me.Level(nextLevelName)

            If nextLevelSetting("Chain") = "Y" Then
                'dv2.RowFilter = String.Format("codelen = {0} * 2 AND {1}", nextLevelSetting("Sequence"), dv.RowFilter)
                'MyObj.WriteLog(dv2.RowFilter)

                Dim col As New DataColumn

                col = New DataColumn
                col.ColumnName = nextLevelName
                col.DataType = GetType(String)
                col.Expression = String.Format("substring(code,1,{0}*2)", nextLevelSetting("Sequence"))

                'dv2:18碼全部
                If dv2.Table.Columns.Contains(nextLevelName) Then
                    dv2.Table.Columns.Remove(nextLevelName)
                End If
                dv2.Table.Columns.Add(col)

                'dv:sku
                If dv.Table.Columns.Contains(nextLevelName) Then
                    dv.Table.Columns.Remove(nextLevelName)
                End If
                dv.Table.Columns.Add(col)
                col.Dispose()

                Dim tmpTable As DataTable = dv2.ToTable(True, nextLevelName)
                Dim skuTable As DataTable = dv.ToTable(True, nextLevelName)

                col = New DataColumn
                col.ColumnName = "LevelName"
                col.DataType = GetType(String)
                col.Expression = String.Format("'{0}'", nextLevelName)
                tmpTable.Columns.Add(col)
                col.Dispose()

                Dim codelist As New List(Of String) 'Need Free
                Dim codeString As String = ""
                For i As Integer = 0 To skuTable.Rows.Count - 1
                    codelist.Add(skuTable.Rows(i)(0).ToString())
                Next
                skuTable.Clear()
                tmpTable.DefaultView.RowFilter = String.Format("[" & nextLevelName & "] in('{0}')", String.Join("','", codelist.ToArray))
                codelist.Clear()

                If Me.dtReport.Rows.Count <> 0 Then
                    For Each dr As DataRowView In tmpTable.DefaultView
                        If Me.dtReport.Rows.Count = 0 Then Exit For
                        Dim dv3 As New DataView(dv.Table)
                        dv3.RowFilter = String.Format("[" & nextLevelName & "]='{0}' AND {1}", dr(nextLevelName), dv.RowFilter)

                        If dv3.Count = 0 Then
                            dv3.Dispose()
                            Continue For
                        End If

                        If monthFormulaList.Count = 0 Then
                            monthFormulaList.Add(String.Format("R[{0}]C:R[{0}]C[11]", (Me.rowCount + 1 - myRowIndex) * 9))
                        Else
                            If (TotalSkuCount - NowSkuCount) <> 0 Then
                                monthFormulaList.Add(String.Format("R[{0}]C:R[{0}]C[11]", (Me.rowCount + 1 - myRowIndex) * 9 + (TotalSkuCount - NowSkuCount) * 2)) '*2=SkuItemCount-LevelItemCount
                            End If
                        End If
                        's.AppendFormat(RenderItem(dv3, dtProdHierarchy.Select(String.Format("LevelName='{0}' AND code = '{1}'", dr("LevelName"), dr(nextLevelName), nextLevelName))(0), showOrder + 1))
                        'MyRow = dtProdHierarchy.Select(String.Format("LevelName='{0}' AND code = '{1}'", dr("LevelName"), dr(nextLevelName), nextLevelName))
                        MyRow = dtProdHierarchy.Rows.Find(New Object() {dr("LevelName"), dr(nextLevelName)})
                        If MyRow IsNot Nothing Then
                            's.AppendFormat(RenderItem(dv3, MyRow, showOrder + 1))
                            xmllist.Add(RenderItem(dv3, MyRow, showOrder + 1))
                        End If
                        dv3.Dispose()
                    Next
                End If

                tmpTable.Rows.Clear()
                tmpTable.Dispose()
            Else
                Dim col As New DataColumn

                col = New DataColumn
                col.ColumnName = nextLevelName
                col.DataType = GetType(String)
                col.Expression = String.Format("substring(code,{0}*2-1,2)", nextLevelSetting("Sequence"))

                'dv2:18碼全部
                If dv2.Table.Columns.Contains(nextLevelName) Then
                    dv2.Table.Columns.Remove(nextLevelName)
                End If
                dv2.Table.Columns.Add(col)

                'dv:sku
                If dv.Table.Columns.Contains(nextLevelName) Then
                    dv.Table.Columns.Remove(nextLevelName)
                End If
                dv.Table.Columns.Add(col)
                col.Dispose()

                Dim tmpTable As DataTable = dv2.ToTable(True, nextLevelName)
                Dim skuTable As DataTable = dv.ToTable(True, nextLevelName)

                col = New DataColumn
                col.ColumnName = "LevelName"
                col.DataType = GetType(String)
                col.Expression = String.Format("'{0}'", nextLevelName)

                tmpTable.Columns.Add(col)
                col.Dispose()

                'MyObj.WriteLog("code_start:" & DateTime.Now.Second & ":" & DateTime.Now.Millisecond)
                Dim codelist As New List(Of String) 'Need Free
                Dim codeString As String = ""
                For i As Integer = 0 To skuTable.Rows.Count - 1
                    codelist.Add(skuTable.Rows(i)(0).ToString())
                Next

                skuTable.Clear()
                'MyObj.WriteLog("code_end:" & DateTime.Now.Second & ":" & DateTime.Now.Millisecond)
                'MyObj.WriteLog(String.Format(nextLevelName & " in('{0}')", String.Join("','", codelist.ToArray)))
                'MyObj.WriteLog("tmpTable_count:" & tmpTable.DefaultView.Count)
                tmpTable.DefaultView.RowFilter = String.Format("[" & nextLevelName & "] in('{0}')", String.Join("','", codelist.ToArray))
                codelist.Clear()

                If Me.dtReport.Rows.Count <> 0 Then
                    For Each dr As DataRowView In tmpTable.DefaultView
                        If Me.dtReport.Rows.Count = 0 Then Exit For
                        Dim dv3 As New DataView(dv.Table)
                        dv3.RowFilter = String.Format("[" & nextLevelName & "]='{0}' AND {1}", dr(nextLevelName), dv.RowFilter)
                        'dv2.Table.Columns.Add(col)'MyObj.WriteLog(dr("LevelName"))

                        If dv3.Count = 0 Then
                            dv3.Dispose()
                            Continue For
                        End If

                        If monthFormulaList.Count = 0 Then
                            monthFormulaList.Add(String.Format("R[{0}]C:R[{0}]C[11]", (Me.rowCount + 1 - myRowIndex) * 9))
                        Else
                            If (TotalSkuCount - NowSkuCount) <> 0 Then
                                monthFormulaList.Add(String.Format("R[{0}]C:R[{0}]C[11]", (Me.rowCount + 1 - myRowIndex) * 9 + (TotalSkuCount - NowSkuCount) * 2)) '*2=SkuItemCount-LevelItemCount
                            End If
                        End If

                        's.AppendFormat(RenderItem(dv3, dtProdHierarchy.Select(String.Format("LevelName='{0}' AND code = '{1}'", dr("LevelName"), dr(nextLevelName), nextLevelName))(0), showOrder + 1))
                        'MyRow = dtProdHierarchy.Select(String.Format("LevelName='{0}' AND code = '{1}'", dr("LevelName"), dr(nextLevelName), nextLevelName))
                        MyRow = dtProdHierarchy.Rows.Find(New Object() {dr("LevelName"), dr(nextLevelName)})
                        If MyRow IsNot Nothing Then
                            's.AppendFormat(RenderItem(dv3, MyRow, showOrder + 1))
                            xmllist.Add(RenderItem(dv3, MyRow, showOrder + 1))
                        End If
                        dv3.Dispose()
                    Next
                End If

                tmpTable.Rows.Clear()
                tmpTable.Dispose()
            End If
        End If

        Dim tempFormulaList As New List(Of String)
        For i As Integer = 0 To monthFormulaList.Count - 1
            If tempFormulaList.IndexOf(monthFormulaList(i)) = -1 Then
                tempFormulaList.Add(monthFormulaList(i))
            End If
        Next
        Dim FormulaList As String = String.Join("+", tempFormulaList.ToArray)

        v.Put("MonthFormula", IIf(FormulaList = "", "0", FormulaList))
        monthFormulaList.Clear()

        's.Insert(0, "a")
        'Dim s2 As New StringBuilder
        'MyObj.WriteLog("vOutput_start:" & DateTime.Now.Second & ":" & DateTime.Now.Millisecond)
        's2.Append(v.Output(Me.vLevel.Template))
        'MyObj.WriteLog("vOutput_end:" & DateTime.Now.Second & ":" & DateTime.Now.Millisecond)
        's2.Append(s.ToString())
        's.Length = 0
        'MyObj.WriteLog("gc_start:" & DateTime.Now.Second & ":" & DateTime.Now.Millisecond)
        System.GC.Collect()
        'System.GC.WaitForPendingFinalizers()
        'MyObj.WriteLog("gc_end:" & DateTime.Now.Second & ":" & DateTime.Now.Millisecond)
        'Return s2.ToString()
        'MyObj.WriteLog("renderLength:" & s.Length())
        'Return v.Output(Me.vLevel.Template) '+ s.ToString()
        xmllist.Insert(0, v.Output(Me.vLevel.Template))
        Return String.Join("", xmllist.ToArray())
    End Function

    Protected ReadOnly Property ShowLevel() As Boolean
        Get
            Return Me.ShowOneSummary = False And Me.MeetingSummary = False
        End Get
    End Property

    Public Function RenderItemV2(ByVal dv As DataView, ByVal current As DataRow) As String
        Dim s As New StringBuilder
        Dim v As New MyVelocity()
        Dim dv2 As New DataView(dtProd)

        Me.rowCount += 1
        Me.TotalRowCount += 9

        Dim monthFormulaList As New List(Of String)

        v.Put("Current", current)
        v.Put("yyData", yyData)
        v.Put("MonthData", MonthData)
        PutMonthData(v)

        Dim codelist As New List(Of String)

        For Each row As DataRowView In dv
            codelist.Add(row("code").ToString())
        Next

        dv2 = New DataView(dtProd2)
        dv2.RowFilter = String.Format("code in ('{0}')", String.Join("','", codelist.ToArray))

        Dim v2 = New MyVelocity()
        'If Search2.ReportMode = ReportModeE.Gross Then
        '    v2 = New MyVelocity("SF-SKU-Gross.xml")
        'Else
        '    v2 = New MyVelocity("SF-SKU.xml")
        'End If

        v2.Put("Page", Me)
        v2.Put("ProdData", dv2)
        v2.Put("AreaCode", MyObj.GetAreaCode())
        v2.Put("yyData", yyData)
        v2.Put("MonthData", MonthData)
        v2.Put("BomData", dtBOM)
        v2.Put("Coefficient", Me.Coefficient)
        PutMonthData(v2)

        Me.rowCount += dv2.Count
        For i As Integer = 1 To dv2.Count
            If i = 1 Then
                monthFormulaList.Add(String.Format("R[{0}]C:R[{0}]C[11]", i * 9))
            Else
                monthFormulaList.Add(String.Format("R[{0}]C:R[{0}]C[11]", (i - 1) * 11 + 9))
            End If
        Next

        Dim AllCount As Integer = Me.rowCount * 11 + 20
        Dim FormulaList As String
        'FormulaList = String.Join("+", monthFormulaList.ToArray)
        'FormulaList = "SUM(((R13C5:R" & AllCount & "C5={0})*(R13C6:R" & AllCount & "C6={1}))*R13C:R" & AllCount & "C)"
        'FormulaList = "sum(if(mod(ROW(R[+8]C:R" & AllCount & "C),11)=ROW(R)-3,R[+8]C:R" & AllCount & "C))"

        FormulaList = "sum(if(mod(ROW(R[+9]C:R" & AllCount & "C),11)=ROW(R)-3,R[+9]C:R" & AllCount & "C))"

        'v.Put("MonthFormula", "SUMPRODUCT((R13C5:R" & AllCount & "C5={0})*(R13C6:R" & AllCount & "C6={1}),R13C:R" & AllCount & "C)")

        v.Put("MonthFormula", IIf(FormulaList = "", "0", FormulaList))

        s.Insert(0, "a")

        If Me.MeetingSummary Then
            If Me.SF_ReportMode = ReportModeE.Gross Then
                Return v2.Output(Me.vSku_Gross.Template) + s.ToString()
            Else
                Return v2.Output(Me.vSku.Template) + s.ToString()
            End If
        Else
            If Me.SF_ReportMode = ReportModeE.Gross Then
                Return v.Output(Me.vLevel.Template) + v2.Output(Me.vSku_Gross.Template) + s.ToString()
            Else
                Return v.Output(Me.vLevel.Template) + v2.Output(Me.vSku.Template) + s.ToString()
            End If
        End If
    End Function

    Dim dtItems As DataTable
    Dim dtFlag As New DataTable
    Dim SkyTypeList As New List(Of String)
    Public Sub SetItemData()
        'MyObj.WriteLog("WorkTime:" & Date.Now.ToString() & ":" & DateTime.Now.Millisecond & "(SetItemData_Start)")

        '建立每個SkuType在每個月份的Flag
        Dim dat As New clsData
        Dim sql As New StringBuilder
        Dim mons As New List(Of String)
        Dim i As Integer
        For i = 1 To 36
            mons.Add(i)
        Next
        SkyTypeList.Clear()
        SkyTypeList.Add("Piping")
        SkyTypeList.Add("SellOut")
        SkyTypeList.Add("PBOM")
        SkyTypeList.Add("TTL(Y-1)")
        SkyTypeList.Add("SellOut(Y-1)")
        SkyTypeList.Add("PBOM(Y-1)")
        SkyTypeList.Add("RetailP") 'Retail Price
        SkyTypeList.Add("ListP")
        SkyTypeList.Add("ListP(Y-1)")

        '建立資料表
        sql.Length = 0
        sql.AppendFormat("SELECT Top 0 SkuType,{0} into #Temp from SFReport ", MyObj.JoinFormat(",", "Cast(Mon{0} as varchar) as Mon{0}", mons))
        For i = 0 To SkyTypeList.Count - 1
            sql.AppendFormat("Insert into #Temp(SkuType) select '{0}' ", SkyTypeList(i))
        Next
        sql.AppendFormat("Select * from #Temp ")
        sql.AppendFormat("drop table #Temp ")
        'MyObj.WriteLog(sql.ToString())
        dtFlag = dat.GetData(sql.ToString())
        dtFlag.PrimaryKey = New DataColumn() {dtFlag.Columns("SkuType")}

        '將各SkuType的Flag填入
        Dim MonIndex As Integer
        Dim MySkuTypeRow As DataRow
        For i = 0 To SkyTypeList.Count - 1
            MonIndex = 0
            For Each dr As DataRow In yyData.Rows
                tmpyy = dr("yy")
                If tmpyy >= Me.SF_Year Then
                    For Each dr2 As DataRow In MonthData.Select(String.Format("yy={0}", tmpyy))
                        MonIndex += 1
                        MySkuTypeRow = dtFlag.Rows.Find(SkyTypeList(i))
                        MySkuTypeRow("Mon" & MonIndex.ToString()) = GetFlag(SkyTypeList(i), dr2("Month"))
                        If MonIndex = 1 Then
                            Me.monDateStart = dr2("Month")
                        End If
                    Next
                End If
            Next
        Next
        'MyObj.WriteLog("WorkTime:" & Date.Now.ToString() & ":" & DateTime.Now.Millisecond & "(SetItemData_End)")
    End Sub

    Public Function GetFlag(ByVal skuType As String, ByVal monthDate As DateTime)
        '如果是Y-1則取前一年資料
        Select Case skuType
            Case "SellOut(Y-1)", "RetailP"
                'Sellout都抓ACT
                flag = "ACT"
            Case "TTL(Y-1)", "ListP(Y-1)", "PBOM(Y-1)"
                'Y-1須判斷是抓ACT還是FCST
                monthDate = monthDate.AddYears(-1)
                If Me.SF_SpecialMonth Then
                    If monthDate.Year < Me.SF_Year Then
                        flag = "ACT"
                    ElseIf monthDate.Year = Me.SF_Year Then
                        If monthDate.Month >= Me.SF_ReferMM Or Me.SF_Month = "B" Then
                            flag = "FCST"
                        Else
                            flag = "ACT"
                        End If
                    Else
                        flag = "FCST"
                    End If
                Else
                    If monthDate.Year < Me.SF_Year Then
                        flag = "ACT"
                    ElseIf monthDate.Year = Me.SF_Year Then
                        If monthDate.Month >= Me.SF_Month Then
                            flag = "FCST"
                        Else
                            flag = "ACT"
                        End If
                    Else
                        flag = "FCST"
                    End If
                End If
            Case "PBOM", "ListP"
                '目前月份則取ACT資料
                If Me.SF_SpecialMonth Then
                    If monthDate.Year < Me.SF_Year Then
                        flag = "ACT"
                    ElseIf monthDate.Year = Me.SF_Year Then
                        If skuType = "ListP" Then
                            If monthDate.Month >= Me.SF_ReferMM Or Me.SF_Month = "B" Then
                                flag = "FCST"
                            Else
                                flag = "ACT"
                            End If
                        Else
                            If monthDate.Month >= Me.SF_ReferMM Or Me.SF_Month = "B" Then
                                flag = "FCST"
                            Else
                                flag = "ACT"
                            End If
                        End If
                    Else
                        flag = "FCST"
                    End If
                Else
                    If skuType = "ListP" Then
                        If monthDate < Convert.ToDateTime(Me.SF_Year & "/" & Me.SF_Month) Then
                            flag = "ACT"
                        Else
                            flag = "FCST"
                        End If
                    Else
                        If monthDate < Convert.ToDateTime(Me.SF_Year & "/" & Me.SF_Month) Then
                            flag = "ACT"
                        Else
                            flag = "FCST"
                        End If
                    End If
                End If
            Case Else
                drMonth = MonthData.Rows.Find(monthDate)
                flag = drMonth("Flag").ToString()
        End Select
        Return flag
    End Function

    'used by velocity
    Dim GetValue As String = ""
    Dim OtherColumn As Integer = 4
    Dim tmpyy As Integer
    Dim GapList As New List(Of String)
    Dim xmlList As New List(Of String)

    Public Function GetItemData(ByVal sapCode As String, ByVal skuType As String) As String
        xmlList.Clear()
        If Me.GapList.Count = 0 Then
            For Each dr As DataRow In yyData.Rows
                Me.GapList.Add(Me.GapMonthFormula(dr("yy")))
            Next
        End If
        Dim GapIndex As Integer = 0
        Dim sfLocked As Boolean = Not Me.SapCodeData.Contains(sapCode) '判斷是否為Locked的SapCode

        For Each dr As DataRow In yyData.Rows
            tmpyy = dr("yy")
            Select Case skuType
                Case "Piping", "SellOut" '要填值,不用Array Range
                    For Each dr2 As DataRow In MonthData.Select(String.Format("yy={0}", tmpyy))
                        GetValue = GetItemDataV2(sapCode, skuType, dr2("Month"))

                        If dr2("flag") = "ACT" OrElse sfLocked = True Then
                            xmlList.Add(IIf(Me.CheckMemo() = "M", "<Cell ss:StyleID=""s42M""", "<Cell"))
                        Else
                            xmlList.Add("<Cell ss:StyleID=""s42E" & Me.CheckMemo() & """")
                        End If
                        xmlList.Add(String.Format(" ss:Formula=""={0}""/>", GetValue))
                    Next
                    If skuType = "Piping" Then
                        xmlList.Add("<Cell ss:ArrayRange=""RC:R[4]C"" ss:Formula=""=RC[1]:R[4]C[1]/12""/>")
                    Else
                        xmlList.Add("<Cell/>")
                    End If
                    xmlList.Add("<Cell ss:Formula=""=SUM(RC[-13]:RC[-2])""/>")
                    xmlList.Add("<Cell ss:Formula=""=SUMIF(R3C[-3]:R3C[-14],R3C,RC[-3]:RC[-14])""/>")
                    If skuType = "Piping" Then
                        xmlList.Add("<Cell ss:ArrayRange=""RC:R[4]C"" ss:Formula=""=RC[-2]:R[4]C[-2]-RC[-1]:R[4]C[-1]""/>")
                    Else
                        xmlList.Add("<Cell/>")
                    End If
                Case "TTL"
                    'Array Range
                    Dim MyString1 As String = ""
                    Dim MyString3 As String = ""
                    For Each dr2 As DataRow In MonthData.Select(String.Format("yy={0}", tmpyy))
                        If dr2("flag") = "ACT" Then
                            MyRowItem = Me.ReportMemo.Rows.Find(New Object() {dr2("Month").Year & "A", skuType, sapCode, dr2("Month").Month})
                        Else
                            colIndex = (dr2("Month").Year - Me.SF_Year) * 12 + dr2("Month").Month
                            MyRowItem = Me.ReportMemo.Rows.Find(New Object() {Me.SF_MonthCode, skuType, sapCode, colIndex})
                        End If
                        If MyRowItem IsNot Nothing Then
                            Me.MemoValue = MyRowItem("MonNotes").ToString()
                        Else
                            Me.MemoValue = ""
                        End If

                        If MyString1 = "" Then
                            MyString1 = IIf(Me.CheckMemo() = "M", "<Cell ss:StyleID=""s42M""", "<Cell")
                        Else
                            MyString3 &= IIf(Me.CheckMemo() = "M", "<Cell ss:StyleID=""s42M""/>", "<Cell/>")
                        End If
                    Next
                    xmlList.Add(MyString1)
                    xmlList.Add(" ss:ArrayRange=""RC:RC[11]"" ss:Formula=""=R[-2]C:R[-2]C[11]+R[-1]C:R[-1]C[11]""/>")
                    xmlList.Add(MyString3)
                    xmlList.Add("<Cell/>")
                    xmlList.Add("<Cell ss:Formula=""=SUM(RC[-13]:RC[-2])""/>")
                    'xmlList.Add(String.Format("<Cell ss:Formula=""={0}""/>", Me.GapList(GapIndex)))
                    xmlList.Add("<Cell ss:Formula=""=SUMIF(R3C[-3]:R3C[-14],R3C,RC[-3]:RC[-14])""/>")
                    xmlList.Add("<Cell/>")
                Case "TTL(Y-1)", "SellOut(Y-1)", "PBOM", "PBOM(Y-1)"
                    'Array Range
                    Dim MyString1 As String = ""
                    Dim MyString2 As String = ""
                    Dim MyString3 As String = ""
                    For Each dr2 As DataRow In MonthData.Select(String.Format("yy={0}", tmpyy))
                        GetValue = GetItemDataV2(sapCode, skuType, dr2("Month"))
                        If MyString2 <> "" Then MyString2 &= ","
                        MyString2 &= GetValue

                        If MyString1 = "" Then
                            MyString1 = IIf(Me.CheckMemo() = "M", "<Cell ss:StyleID=""s42M""", "<Cell")
                        Else
                            MyString3 &= IIf(Me.CheckMemo() = "M", "<Cell ss:StyleID=""s42M""/>", "<Cell/>")
                        End If
                    Next
                    xmlList.Add(MyString1)
                    'Muti Data
                    xmlList.Add(" ss:ArrayRange=""RC:RC[11]"" ss:Formula=""={" & MyString2 & "}""/>")
                    xmlList.Add(MyString3)
                    If skuType = "SellOut(Y-1)" Then
                        xmlList.Add("<Cell ss:ArrayRange=""RC:R[2]C"" ss:Formula=""=RC[1]:R[2]C[1]/12""/>")
                    Else
                        xmlList.Add("<Cell/>")
                    End If
                    xmlList.Add("<Cell ss:Formula=""=SUM(RC[-13]:RC[-2])""/>")
                    xmlList.Add("<Cell ss:Formula=""=SUMIF(R3C[-3]:R3C[-14],R3C,RC[-3]:RC[-14])""/>")
                    If skuType = "SellOut(Y-1)" Then
                        xmlList.Add("<Cell ss:ArrayRange=""RC:R[2]C"" ss:Formula=""=RC[-2]:R[2]C[-2]-RC[-1]:R[2]C[-1]""/>")
                    Else
                        xmlList.Add("<Cell/>")
                    End If
                Case "ListP" '要填值,不用Array Range
                    For Each dr2 As DataRow In MonthData.Select(String.Format("yy={0}", tmpyy))
                        GetValue = GetItemDataV2(sapCode, skuType, dr2("Month"))
                        If dr2("flag") = "ACT" OrElse sfLocked = True Then
                            xmlList.Add("<Cell ss:StyleID=""s47R2" & Me.CheckMemo() & """")
                        Else
                            xmlList.Add("<Cell ss:StyleID=""s47R2E" & Me.CheckMemo() & """")
                        End If
                        xmlList.Add(String.Format(" ss:Formula=""={0}""/>", GetValue))
                    Next
                    For i As Integer = 1 To OtherColumn
                        xmlList.Add("<Cell ss:StyleID=""s47""/>")
                    Next
            End Select
            GapIndex += 1
        Next

        Return String.Join("", xmlList.ToArray())
    End Function

    Dim drMonth As DataRow
    Dim flag As String
    Dim Memoflag As String
    Dim value As Object
    Dim value2 As Single = 0
    Dim value3 As Object
    Dim OldCode, OldCode2 As String
    Dim dtIndex As Integer
    Dim MyRowItem As DataRow
    Dim colIndex As Integer
    Dim monDateStart As DateTime
    Public Function GetItemData(ByVal sapCode As String, ByVal skuType As String, ByVal monthDate As DateTime) As String
        Return GetItemDataV2(sapCode, skuType, monthDate)
    End Function

    Public Function GetItemDataV2(ByVal sapCode As String, ByVal skuType As String, ByVal monthDate As DateTime) As String
        Me.MemoValue = ""
        Dim OriginalMonthDate As DateTime = monthDate
        If skuType.IndexOf("(Y-1)") <> -1 Then
            monthDate = monthDate.AddYears(-1)
        End If
        drMonth = dtFlag.Rows.Find(skuType)

        colIndex = DateDiff(DateInterval.Month, Me.monDateStart, OriginalMonthDate) + 1

        If colIndex <= 0 Or colIndex > 36 Then
            flag = "ACT"
            Memoflag = "ACT"
        Else
            drMonth = dtFlag.Rows.Find("Piping")
            Memoflag = drMonth("Mon" & colIndex).ToString()
            drMonth = dtFlag.Rows.Find(skuType)
            flag = drMonth("Mon" & colIndex).ToString()
        End If

        'get index
        value = 0
        If flag = "ACT" Then
            dtIndex = Me.SF_Year - monthDate.Year
            Try
                value = Me.GetActData(skuType, dtIndex, sapCode, monthDate)
                MyRowItem = dtProd2.Rows.Find(sapCode)
                OldCode = MyRowItem("OldCode").ToString()
                OldCode2 = MyRowItem("OldCode2").ToString()

                '若有OldCode,且此OldCode不在此版本中,則用OldCode再取一次值
                If OldCode <> "" And Not Me.dtAllSapCode.Rows.Contains(OldCode) Then
                    Select Case skuType
                        Case "ListP"
                            If value = 0 Then
                                value = Me.GetActData(skuType, dtIndex, OldCode, monthDate)
                            End If
                        Case Else
                            value += Me.GetActData(skuType, dtIndex, OldCode, monthDate)
                    End Select
                End If
                '若有OldCode2,且此OldCode2不在此版本中,則用OldCode2再取一次值
                If OldCode2 <> "" And Not Me.dtAllSapCode.Rows.Contains(OldCode2) Then
                    Select Case skuType
                        Case "ListP"
                            If value = 0 Then
                                value = Me.GetActData(skuType, dtIndex, OldCode2, monthDate)
                            End If
                        Case Else
                            value += Me.GetActData(skuType, dtIndex, OldCode2, monthDate)
                    End Select
                End If
            Catch ex As Exception
                'MyObj.WriteLog(ex.ToString())
                value = 0
            End Try
        Else
            colIndex = (monthDate.Year - Me.SF_Year) * 12 + monthDate.Month
            value2 = 0
            value3 = 0
            Select Case skuType
                Case "PBOM", "TTL(Y-1)"

                    For Each dr As DataRow In dtBOM.Select(String.Format("BOMSapCode='{0}' ", sapCode))
                        If dr("Mon" & colIndex) Is DBNull.Value Then Continue For
                        value2 += dr("Mon" & colIndex) * dr("Qty")
                    Next

                    If skuType = "TTL(Y-1)" Then
                        'MyRowItem = dtReport.Rows.Find(New Object() {sapCode, "Piping"})
                        'If MyRowItem IsNot Nothing Then
                        '    value3 = MyRowItem("Mon" & colIndex)
                        '    value2 += IIf(value3 Is DBNull.Value, "0", value3)
                        'End If

                        MyRowItem = dtReport.Rows.Find(New Object() {sapCode, "SellOut"})
                        If MyRowItem IsNot Nothing Then
                            value3 = MyRowItem("Mon" & colIndex)
                            value2 += IIf(value3 Is DBNull.Value, "0", value3)
                        End If
                    End If
                    value = value2

                Case ""
                    value = 0
                Case Else
                    'MyRowItem = dtReport.Select(String.Format("SapCode='{0}' AND SkuType='{1}' ", sapCode, skuType))
                    MyRowItem = dtReport.Rows.Find(New Object() {sapCode, skuType.Replace("(Y-1)", "")})
                    If MyRowItem IsNot Nothing Then
                        value = MyRowItem("Mon" & colIndex)
                    Else
                        value = 0
                    End If
            End Select

            'MyObj.WriteLog(String.Format("{0} {1} Mon{2} {3}", sapCode, skuType, colIndex, value.ToString()))
            'Return IIf(value Is DBNull.Value, "0", value)
        End If

        '註解
        If Memoflag = "ACT" Then
            MyRowItem = Me.ReportMemo.Rows.Find(New Object() {OriginalMonthDate.Year & "A", skuType, sapCode, OriginalMonthDate.Month})
            If MyRowItem IsNot Nothing Then
                Me.MemoValue = MyRowItem("MonNotes").ToString()
            Else
                Me.MemoValue = ""
            End If
        Else
            MyRowItem = Me.ReportMemo.Rows.Find(New Object() {Me.SF_MonthCode, skuType, sapCode, colIndex})
            If MyRowItem IsNot Nothing Then
                Me.MemoValue = MyRowItem("MonNotes").ToString()
            Else
                Me.MemoValue = ""
            End If
        End If
        value = IIf(value Is DBNull.Value, 0, value)
        Return IIf(IsNumeric(value), value, 0)
    End Function

    Dim MemoValue As String = ""
    Public Function CheckMemo()
        If Me.MemoValue = "" OrElse Me.MemoValue Is Nothing OrElse Me.SF_ReportMode = ReportModeE.Gross Then
            Return ""
        Else
            Return "M"
        End If
    End Function

    Dim valueAct, value3Act As Object
    Dim MyRowAct As DataRow
    Protected Function GetActData(ByVal skuType As String, ByVal dtIndex As Integer, ByVal sapCode As String, ByVal monthDate As DateTime) As Decimal
        valueAct = 0
        value3Act = 0

        Select Case skuType
            Case "TTL(Y-1)"
                'MyRowAct = dtACTs(dtIndex).Rows.Find(New Object() {sapCode, "Piping"})
                'If MyRowAct IsNot Nothing Then
                '    value3Act = MyRowAct("Mon" & monthDate.Month)
                '    valueAct += IIf(value3Act Is DBNull.Value, "0", value3Act)
                'End If
                MyRowAct = dtACTs(dtIndex).Rows.Find(New Object() {sapCode, "SellOut"})
                If MyRowAct IsNot Nothing Then
                    value3Act = MyRowAct("Mon" & monthDate.Month)
                    valueAct += IIf(value3Act Is DBNull.Value, "0", value3Act)
                End If
                'MyRowAct = dtACTs(dtIndex).Rows.Find(New Object() {sapCode, "PBOM"})
                'If MyRowAct IsNot Nothing Then
                '    value3Act = MyRowAct("Mon" & monthDate.Month)
                '    valueAct += IIf(value3Act Is DBNull.Value, "0", value3Act)
                'End If
                For Each dr As DataRow In dtACTPBOM(dtIndex).Select(String.Format("BOMSapCode='{0}' ", sapCode))
                    If dr("Mon" & colIndex) Is DBNull.Value Then Continue For
                    value3Act = dr("Mon" & colIndex) * dr("Qty")
                    valueAct += IIf(value3Act Is DBNull.Value, "0", value3Act)
                Next
            Case Else
                If skuType = "PBOM" Then
                    For Each dr As DataRow In dtACTPBOM(dtIndex).Select(String.Format("BOMSapCode='{0}' ", sapCode))
                        If dr("Mon" & colIndex) Is DBNull.Value Then Continue For
                        valueAct += dr("Mon" & colIndex) * dr("Qty")
                    Next
                Else
                    MyRowAct = dtACTs(dtIndex).Rows.Find(New Object() {sapCode, skuType.Replace("(Y-1)", "")})

                    If MyRowAct IsNot Nothing Then
                        valueAct = MyRowAct("Mon" & monthDate.Month)
                    Else
                        valueAct = 0
                    End If
                End If
        End Select
        Return IIf(IsNumeric(valueAct), valueAct, 0)
    End Function
#End Region

#Region "屬性"
    Protected _MonthData As DataTable
    Public Property MonthData()
        Get
            Return Me._MonthData
        End Get
        Set(ByVal value)
            Me._MonthData = value
        End Set
    End Property

    Protected _ColumnSection As String
    Public Property ColumnSection()
        Get
            Return Me._ColumnSection
        End Get
        Set(ByVal value)
            Me._ColumnSection = value
        End Set
    End Property

    '標題Signature名稱
    Protected _SF_SignatureText As String
    Public Property SF_SignatureText() As String
        Get
            Return Me._SF_SignatureText
        End Get
        Set(ByVal value As String)
            Me._SF_SignatureText = value
        End Set
    End Property

    '報表類型(Normal、Gross、Meeting)
    Protected _SF_ReportMode As String
    Public Property SF_ReportMode() As String
        Get
            Return Me._SF_ReportMode
        End Get
        Set(ByVal value As String)
            Me._SF_ReportMode = value
        End Set
    End Property

    Protected _ViewPoint As String
    Public Property ViewPoint() As String
        Get
            Return Me._ViewPoint
        End Get
        Set(ByVal value As String)
            Me._ViewPoint = value
        End Set
    End Property

    Protected _HeaderTB As String
    Public Property HeaderTB() As String
        Get
            Return Me._HeaderTB
        End Get
        Set(ByVal value As String)
            Me._HeaderTB = value
        End Set
    End Property

    Protected _SF_Division As String
    Public Property SF_Division()
        Get
            Return Me._SF_Division
        End Get
        Set(ByVal value)
            Me._SF_Division = value
        End Set
    End Property

    Protected _SF_MonthCode As String
    Public Property SF_MonthCode()
        Get
            Return Me._SF_MonthCode
        End Get
        Set(ByVal value)
            Me._SF_MonthCode = value
        End Set
    End Property

    Protected _SF_Version1 As String
    Public Property SF_Version1()
        Get
            Return Me._SF_Version1
        End Get
        Set(ByVal value)
            Me._SF_Version1 = value
        End Set
    End Property

    Protected _SF_Version2 As String
    Public Property SF_Version2()
        Get
            Return Me._SF_Version2
        End Get
        Set(ByVal value)
            Me._SF_Version2 = value
        End Set
    End Property

    Protected _SF_Signature As String
    Public Property SF_Signature()
        Get
            Return Me._SF_Signature
        End Get
        Set(ByVal value)
            Me._SF_Signature = value
        End Set
    End Property

    Protected _SF_SecondLevelName As String
    Public Property SF_SecondLevelName()
        Get
            Return Me._SF_SecondLevelName
        End Get
        Set(ByVal value)
            Me._SF_SecondLevelName = value
        End Set
    End Property

    Protected _SF_SecondLevelValue As String
    Public Property SF_SecondLevelValue()
        Get
            Return Me._SF_SecondLevelValue
        End Get
        Set(ByVal value)
            Me._SF_SecondLevelValue = value
        End Set
    End Property

    Protected _SF_sqlArray As String(,)
    Public Property SF_sqlArray()
        Get
            Return Me._SF_sqlArray
        End Get
        Set(ByVal value)
            Me._SF_sqlArray = value
        End Set
    End Property

    Protected _SF_Channel As String
    Public Property SF_Channel()
        Get
            Return Me._SF_Channel
        End Get
        Set(ByVal value)
            Me._SF_Channel = value
        End Set
    End Property

    Protected _SF_Year As String
    Public Property SF_Year()
        Get
            Return Me._SF_Year
        End Get
        Set(ByVal value)
            Me._SF_Year = value
        End Set
    End Property

    Protected _SF_Month As String
    Public Property SF_Month()
        Get
            Return Me._SF_Month
        End Get
        Set(ByVal value)
            Me._SF_Month = value
        End Set
    End Property

    Protected _SF_SpecialMonth As Boolean
    Public Property SF_SpecialMonth()
        Get
            Return Me._SF_SpecialMonth
        End Get
        Set(ByVal value)
            Me._SF_SpecialMonth = value
        End Set
    End Property

    Protected _SF_FCSTMonth As String
    Public Property SF_FCSTMonth()
        Get
            Return Me._SF_FCSTMonth
        End Get
        Set(ByVal value)
            Me._SF_FCSTMonth = value
        End Set
    End Property

    Protected _SF_ReferYYMM As String
    Public Property SF_ReferYYMM()
        Get
            Return Me._SF_ReferYYMM
        End Get
        Set(ByVal value)
            Me._SF_ReferYYMM = value
        End Set
    End Property

    Protected _SF_ReferMM As String
    Public Property SF_ReferMM()
        Get
            Return Me._SF_ReferMM
        End Get
        Set(ByVal value)
            Me._SF_ReferMM = value
        End Set
    End Property

    Protected _SF_MMPP As String
    Public Property SF_MMPP()
        Get
            Return Me._SF_MMPP
        End Get
        Set(ByVal value)
            Me._SF_MMPP = value
        End Set
    End Property

    Protected _SF_IsCatagory As String
    Public Property SF_IsCatagory()
        Get
            Return Me._SF_IsCatagory
        End Get
        Set(ByVal value)
            Me._SF_IsCatagory = value
        End Set
    End Property

    Protected _SF_BasicSignature As String
    Public Property SF_BasicSignature()
        Get
            Return Me._SF_BasicSignature
        End Get
        Set(ByVal value)
            Me._SF_BasicSignature = value
        End Set
    End Property

    Protected _SF_DisplayYear As String
    Public Property SF_DisplayYear()
        Get
            Return Me._SF_DisplayYear
        End Get
        Set(ByVal value)
            Me._SF_DisplayYear = value
        End Set
    End Property

    Protected _SapCodeDT As DataTable
    Public Property SapCodeDT() As DataTable
        Get
            Return Me._SapCodeDT
        End Get
        Set(ByVal value As DataTable)
            Me._SapCodeDT = value
        End Set
    End Property

    Public ReadOnly Property SF_IsMutiSignature()
        Get
            Return Me.IsMutiSignature
        End Get
    End Property

    Protected _SF_CatagoryString As String
    Public Property SF_CatagoryString()
        Get
            Return Me._SF_CatagoryString
        End Get
        Set(ByVal value)
            Me._SF_CatagoryString = value
        End Set
    End Property

    Protected _ReportMemo As DataTable
    Public Property ReportMemo()
        Get
            Return Me._ReportMemo
        End Get
        Set(ByVal value)
            Me._ReportMemo = value
        End Set
    End Property

    Protected _ChannelPercentage As DataTable
    Public Property ChannelPercentage()
        Get
            Return Me._ChannelPercentage
        End Get
        Set(ByVal value)
            Me._ChannelPercentage = value
        End Set
    End Property

    Protected _ReportXML As New List(Of String)
    Public Property ReportXML() As List(Of String)
        Get
            Return Me._ReportXML
        End Get
        Set(ByVal value As List(Of String))
            Me._ReportXML = value
        End Set
    End Property

    Protected _MeetingSummary As Boolean
    Public Property MeetingSummary() As Boolean
        Get
            Return Me._MeetingSummary
        End Get
        Set(ByVal value As Boolean)
            Me._MeetingSummary = value
        End Set
    End Property

    Protected _ShowOneSummary As Boolean
    Public Property ShowOneSummary() As Boolean
        Get
            Return Me._ShowOneSummary
        End Get
        Set(ByVal value As Boolean)
            Me._ShowOneSummary = value
        End Set
    End Property

    Protected _SF_Lock As Boolean
    Public Property SF_Lock() As Boolean
        Get
            Return Me._SF_Lock
        End Get
        Set(ByVal value As Boolean)
            Me._SF_Lock = value
        End Set
    End Property

    Protected _SF_SheetName As String = ""
    Public Property SF_SheetName() As String
        Get
            If Me._SF_SheetName.ToString().Trim() = "" Then
                Me._SF_SheetName = "SellOut_Input"
            End If
            Return Me.ReportXML.Count + 1 & "." & Me._SF_SheetName
        End Get
        Set(ByVal value As String)
            Me._SF_SheetName = value
        End Set
    End Property

    Protected ReadOnly Property OldReportName() As String
        Get
            Return String.Format("SFReport{0}", Me.SF_MonthCode)
        End Get
    End Property

    Protected ReadOnly Property ReportName() As String
        Get
            Return String.Format("SO_SFReport{0}", Me.SF_MonthCode)
        End Get
    End Property

    Protected ReadOnly Property ACTReportName(ByVal index As Integer) As String
        Get
            Return String.Format("SFReport{0}A", Me.SF_Year + index)
        End Get
    End Property

    Protected ReadOnly Property BOMTableName() As String
        Get
            Return String.Format("ProductBOM{0}", Me.SF_MonthCode)
            'If IsNumeric(Me.ProductHierarchyControl1.MonthCode) Then
            '    Return String.Format("ProductBOM{0}", Me.ProductHierarchyControl1.MonthCode)
            'Else
            '    'BudGet、Trend 使用當時儲存時的月份
            '    Return String.Format("ProductBOM{0}", Me.ProductHierarchyControl1.ReferYYMM)
            'End If
        End Get
    End Property

    Protected ReadOnly Property UserSelectedLevels() As List(Of UserSelectedLevel)
        Get
            If _UserSelectedLevels.Count = 0 Then
                For i As Integer = 0 To Me.SF_sqlArray.GetUpperBound(0)
                    Dim u As UserSelectedLevel

                    u.LevelName = Me.SF_sqlArray(i, 0).Replace("[", "").Replace("]", "")
                    u.LevelValue = Me.SF_sqlArray(i, 1)

                    _UserSelectedLevels.Add(u)
                Next
            End If

            Return _UserSelectedLevels
        End Get
    End Property

    Protected ReadOnly Property UserSelectedLevelString() As String
        Get
            Dim sql As New StringBuilder
            If Not Me.IsMutiSignature Then
                sql.AppendFormat("AND left(pd.ProdHierarchy,2) = '{0}' ", Me.UserSelectedLevels(0).LevelValue)
            End If
            sql.AppendFormat(Me.SF_CatagoryString.Replace("Hierarchy", "pd.ProdHierarchy"))

            For Each u As UserSelectedLevel In Me.UserSelectedLevels
                Dim setting As DataRow = Me.Level(u.LevelName)
                If u.LevelValue = String.Empty Then Continue For

                If u.LevelName = "Signature" Then
                    sql.AppendFormat("AND substring(pd.ProdHierarchy,1,2) in ({0}) ", MyObj.JoinFormat(",", "'{0}'", Me.SigData))
                Else
                    If setting("Chain") = "Y" Then
                        sql.AppendFormat("AND substring(pd.ProdHierarchy,1,{0}*2) = '{1}' ", setting("Sequence"), u.LevelValue)
                    Else
                        sql.AppendFormat("AND substring(pd.ProdHierarchy,{0}*2-1,2) = '{1}' ", setting("Sequence"), u.LevelValue)
                    End If
                End If
            Next
            'MyObj.WriteLog(sql.ToString())
            Return sql.ToString()
        End Get
    End Property

    Public ReadOnly Property Level(ByVal name As String) As DataRow
        Get
            Return dtProdLevel.Select(String.Format("LevelName = '{0}'", name))(0)
        End Get
    End Property

    Protected Sub PutMonthData(ByVal v As MyVelocity)
        v.Put("MonthData", Me.MonthData)
    End Sub

    Public Function GapMonthFormula(ByVal GapYear As Integer) As String
        Dim formulas As New List(Of String)
        Dim drMonth As DataRow
        Dim GapMonth As DateTime

        For i As Integer = 1 To 12
            GapMonth = New Date(GapYear, i, 1)
            'drMonth = MonthData.Select(String.Format("Month='{0}'", GapMonth))
            drMonth = MonthData.Rows.Find(GapMonth)
            If drMonth IsNot Nothing AndAlso drMonth("Flag").ToString() = "ACT" Then
                formulas.Add(String.Format("RC[{0}]", -15 + i))
            End If
        Next
        If formulas.Count = 0 Then
            Return 0
        Else
            Return String.Join("+", formulas.ToArray)
        End If
    End Function

    Protected ReadOnly Property yyData() As DataTable
        Get
            Return MonthData.DefaultView.ToTable(True, "yy")
        End Get
    End Property

    Public ReadOnly Property dtProdLevel() As DataTable
        Get
            If _dtProdLevel Is Nothing Then
                Dim dat As New clsData
                Dim sql As New StringBuilder
                Dim params As New List(Of SqlParameter)

                sql.AppendFormat("SELECT *,Cast(Sequence as varchar)+'G' as LevelName2 ")
                sql.AppendFormat("FROM ProductLevel ")

                _dtProdLevel = dat.GetData(sql.ToString())

                'for xml
                _dtProdLevel.Columns.Add("xlsStyleID")
            End If

            Return _dtProdLevel
        End Get
    End Property

    Public Structure UserSelectedLevel
        Public LevelName As String
        Public LevelValue As String
    End Structure

    Public Coefficient As String
#End Region

#Region "Group Property"
    Private _SapCodeData As List(Of String)
    Protected ReadOnly Property SapCodeData() As List(Of String)
        Get
            If Me._SapCodeData Is Nothing Then
                Me._SapCodeData = Me.sfmanager.GetGroupSapCodes(Me.SF_MonthCode, Me.SF_Division, Me.SF_BasicSignature, Me.HeaderTB)
            End If
            Return Me._SapCodeData
        End Get
    End Property

    Private _SigData As List(Of String)
    Protected ReadOnly Property SigData() As List(Of String)
        Get
            If Me._SigData Is Nothing Then
                Me._SigData = Me.stManager.GetGroupSignatures(Me.ViewPoint, Me.SF_Division, Me.SF_BasicSignature)
            End If
            Return Me._SigData
        End Get
    End Property

    Private _ChlData As List(Of String)
    Protected ReadOnly Property ChlData() As List(Of String)
        Get
            If Me._ChlData Is Nothing Then
                If Me.SF_Channel = String.Empty Then
                    Me._ChlData = Me.cmanager.GetGroupChannels(Me.SF_Division, Me.SF_BasicSignature, MyObj.SelectAllValue)
                Else
                    Me._ChlData = Me.cmanager.GetGroupChannels(Me.SF_Division, Me.SF_BasicSignature, Me.SF_Channel)
                End If
            End If
            Return Me._ChlData
        End Get
    End Property

    '判斷是否為Total或Group Signature
    Public ReadOnly Property IsMutiSignature() As Boolean
        Get
            If Me.SF_Signature = String.Empty OrElse Me.SF_Signature.IndexOf(MyObj.GroupMark) <> -1 Then
                Return True
            Else
                Return False
            End If
        End Get
    End Property

    '判斷是否為All或Group Channel
    Public ReadOnly Property IsMutiChannel() As Boolean
        Get
            If Me.SF_Channel = String.Empty OrElse Me.SF_Channel.IndexOf(MyObj.GroupMark) <> -1 Then
                Return True
            Else
                Return False
            End If
        End Get
    End Property
#End Region

End Class

#End Region
